MariaDB - LEFT JOIN не возвращает все строки

Question

(Прежде чем пометить это как дубликат, прочтите вопрос).

У меня две разные таблицы,Bot а также Switch.

Bot
ID | Info | Active
------------------
0  | abc  |     1
1  | def  |     1

Switch
Date       | Activated | BotID | User
-------------------------------------
2020-01-01 |         1 |     0 | John
2020-01-02 |         0 |     0 | John

Для каждого бота я хотел бы получить его последний статус, что означает: для каждого бота я хотел бы знать, есть ли последняя строка Activated поле было 1 или 0. Для того, чтобы вернуть результат тем ботам, у которых нет записи вSwitch стол, я попытался использовать LEFT JOINзаявление. Вот запрос:

SELECT IFNULL(x.Activated, 0) AS Following, b.ID, b.Info
FROM bot b
LEFT JOIN (
    SELECT *
    FROM switch s1
    WHERE s1.Date IN (
        SELECT MAX(s.Date)
        FROM switch s
        WHERE s.User = 'SomeUsername'
        GROUP BY s.Bot
    )
) AS x ON x.BotID = b.ID
WHERE b.Active = 1

Мой ожидаемый результат:

Following | ID | Info
---------------------
        0 | 0  | abc
        0 | 1  | def

Дело в том, что я получаю не все строки, вместо этого этот запрос просто возвращает одну строку:

Following | ID | Info
---------------------
        0 | 0  | abc

Это странно, поскольку я использую LEFT JOIN. Чтобы понять, что происходит, я отдельно запустил

SELECT * 
FROM bot

а также

SELECT *
FROM switch s1
WHERE s1.Date IN (
    SELECT MAX(s.Date)
    FROM switch s
    WHERE s.User = 'SomeUsername'
    GROUP BY s.Bot
)

Конечно, первый запрос возвращает:

ID | Info
---------
0  | abc
1  | def

Пока второй возвращается:

Date       | Activated | BotID | User
-------------------------------------
2020-01-02 |         0 |     0 | John

Я действительно не могу понять, почему LEFT JOIN не держит оба Botряды. Я проверил этот вопрос, но я не использую никаких внешнихWHEREтак что это не мой случай.
Заранее спасибо!

Answer 1

Поскольку вы используете Maria 10.5, вы можете использовать аналитику, чтобы облегчить себе жизнь:

WITH x AS (
  SELECT 
    COALESCE(s.Activated, 0) AS Following,
    b.ID, 
    b.Info, 
    ROW_NUMBER() OVER(PARTITION BY b.ID ORDER BY s.Date DESC) rn
  FROM 
    bots b
    LEFT JOIN switch s ON s.Bot = b.ID
  WHERE b.Active = 1
)

SELECT * FROM x WHERE rn = 1

Это должно быть довольно легко редактировать - по сути, это стандартные боты с левым соединением: switch. Иногда вы получаете переключатель, иногда нет. Когда вы получаете переключатель, вы получаете самую последнюю дату из row_number. Если вы удалитеWHERE rn = 1вы увидите, что получаете все даты, но есть столбец rn, который представляет собой увеличивающийся счетчик, который перезапускается с 1 при изменении идентификатора бота. Вrn = 1 строки - это то, что вы хотите, потому что счетчик увеличивается по мере того, как дата идет от недавнего к прошлому

Добавление дополнительных столбцов к этому - это просто случай добавления их к выделению внутри блока WITH

COALESCEнет ничего волшебного; это похоже на IFNULL, но может иметь множество аргументов. Он работает слева направо, возвращая первый, который не равен нулю. При использовании с двумя аргументами он похож на IFNULL, но имеет то преимущество, что он работает во всех основных базах данных, соответствующих спецификации, тогда как IFNULL/NVL/ISNULL меняет имя все время

---

Если аналитика недоступна, типичная "получить последнюю строку" выглядит так:

WITH switchlatest AS (
    SELECT s.*
    FROM
      switch s
      INNER JOIN (SELECT bot, MAX(date) maxd FROM switch GROUP BY bot) maxd
      ON s.bot = maxd.bot AND s.date = maxd.date

)

Вы можете оставить присоединиться к этому switchlatest так же, как и вы switch

Он ведет себя немного иначе, чем rownum, если есть несколько дат, которые совпадают с true max (все они возвращаются, rownum route возвращает только одну)

Общий шаблон для версий базы данных, которые не поддерживают WITH

WITH alias AS 
(
   SELECT columns FROM ...
)
SELECT ... 
FROM 
  table 
  JOIN 
  alias 
  ON ...

Вы копируете слово "псевдоним" после закрывающей скобки:

(
   SELECT columns FROM ...
) alias

Затем вы копируете все это и вставляете поверх псевдонима в запросе:

SELECT ... 
FROM 
  table 
  JOIN 
  (
     SELECT columns FROM ...
  ) alias
  ON 
...

Or you can take the WITH and turn it into a view (Replace the WITH with CREATE VIEW) - might as well have it on hand without having to code it into every query,  because you clearly have a business need for "the latest switch" 

Starting to think it might be easier to just upgrade the db!

Answer 2

Ваш запрос должен делать то, что вы хотите, как вы можете видеть в этой скрипте db (я исправил проблемы с именами столбцов и немного изменил данные, чтобы было понятно, что он работает). Так что проблема либо в ваших данных, либо в вашем слишком упрощенном запросе.

Однако позвольте мне предложить оптимизацию. Поскольку вам нужен только один столбец из таблицыswitch, вы можете использовать коррелированный подзапрос с ограничением строк вместо соединения:

select 
    coalesce(
        (
            select s.activated
            from switch s
            where s.user = 'SomeUsername' and s.botid = b.id
            order by s.date desc limit 1
        ),
        0
    ) as following,
    b.*
from bot

Я ожидал, что это будет более эффективно, чем исходная версия с объединениями и фильтрацией.

Если вы используете MySQL 8.0, вы можете использовать row_number():

select coalesce(s.activated, 0) as following, b.*
from bot b
left join (
    select s.*, row_number() over(partition by botid order by date desc) rn
    from switch s
    where user = 'SomeUsername'
) s on s.botid = b.id and rn = 1

Демо по DB Fiddle.

---

Если ваша версия MySQL / MariaDB не поддерживает оконные функции, и вам нужно более одного столбца из switch, то вы можете попытаться сформулировать запрос немного по-другому, чтобы увидеть, сохраняется ли проблема. Рассмотрите возможность фильтрации вon пункт left join, вот так:

SELECT ...
FROM bot b
LEFT JOIN switch s
    ON s.botid = b.id
    AND s.date = (
        SELECT MAX(s1.date)
        FROM switch s1
        WHERE s1.User = 'SomeUsername' AND s1.botid = s.botid
    )
WHERE b.Active = 1