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

您的查询应该做您想做的事情，正如您在这个db fiddle中看到的那样(我修复了列名的问题，并稍微更改了数据，所以很容易理解它的工作原理(。所以问题要么是你的数据，要么是你过于简化了查询。

不过，让我建议进行优化。由于您只需要表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中的多个列，那么您可以尝试用不同的方式表达查询，看看问题是否仍然存在。考虑在left join的on子句中进行过滤，如下所示：

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

Answer 2

当您使用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列是一个递增计数器，当机器人id改变时，它会从1重新启动。rn = 1行是您想要的，因为计数器随着日期从最近到过去的而递增

向其中添加更多列只是将它们添加到WITH块内的select中的一种情况

COALESCE并不是什么神奇的东西；它类似于IFNULL，但可能有大量的参数。它从左到右依次返回第一个非null。当与2个argument一起使用时，它就像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
)

你可以像加入switch一样加入这个switchlatest

如果有多个日期与true max相同(它们都被返回，rownum路由只返回一个(，则它的行为与rownum有点不同。

不支持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!