gpt4 book ai didi

mysql - 如何在两个不同的数据库上从内部连接这两个表显示 NULL 或零?

转载 作者:行者123 更新时间:2023-12-01 00:54:08 26 4
gpt4 key购买 nike

我在 2 个不同的数据库上有 2 个表:

db1.table1

+--------------+--------------------------+
| Username | Message |
+--------------+--------------------------+
| jamesbond | I need some help |
| jamesbond | I need some help |
| jamesbond | I need some help |
| jamesbond | Mission accomplished |
+--------------+--------------------------+

db2.table2

+--------------------------+--------------+
| Message | Status |
+--------------------------+--------------+
| I need some help | Ok |
| I need some help | Ok |
| I need some help | Bad |
+--------------------------+--------------+

当我使用以下 SQL 语法“INNER JOIN”这些表时:

SELECT A.Username, A.Message
SUM(CASE WHEN `status` = 'Ok' THEN 1 ELSE 0 END) AS StatOK,
SUM(CASE WHEN `status` = 'Bad' THEN 1 ELSE 0 END) AS StatBAD
FROM db1.table1 as A
INNER JOIN db2.table2 as B
ON A.Message = B.Message
WHERE A.Username = 'jamesbond'
GROUP BY A.Username, A.Message

我得到了这个结果:

+--------------+--------------------------+--------+---------+
| Username | Message | StatOK | StatBAD |
+--------------+--------------------------+--------+---------+
| jamesbond | I need some help | 2 | 1 |
+--------------+--------------------------+--------+---------+

如何得到这样的结果(DB2 上没有状态的消息仍然出现,但 SUM 结果可以是零或 NULL):

+--------------+--------------------------+--------+---------+
| Username | Message | StatOK | StatBAD |
+--------------+--------------------------+--------+---------+
| jamesbond | I need some help | 2 | 1 |
| jamesbond | Mission accomplished | NULL | NULL |
+--------------+--------------------------+--------+---------+

最佳答案

您需要一个Left Outer Join,它将从 Left 表中获取行,如果它与 Right 表不匹配,那么您将得到 NULL。

SELECT A.Username, A.Message
SUM(CASE WHEN `status` = 'Ok' THEN 1 ELSE 0 END) AS StatOK,
SUM(CASE WHEN `status` = 'Bad' THEN 1 ELSE 0 END) AS StatBAD
FROM db1.table1 as A
LEFT OUTER JOIN db2.table2 as B
ON A.Message = B.Message
WHERE A.Username = 'jamesbond'
GROUP BY A.Username, A.Message

关于mysql - 如何在两个不同的数据库上从内部连接这两个表显示 NULL 或零?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12701993/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com