计算左连接中的连接行数

我尝试用 SQL 编写一个聚合查询，它返回一个表中所有连接到给定记录的记录的计数; 如果没有记录连接到给定记录，那么该记录的结果应该是 0:

百科

我的数据库如下所示(不幸的是，我无法更改结构) :

MESSAGE
----------------------------------------------
MESSAGEID   SENDER        SUBJECT
----------------------------------------------
1           Tim           Rabbit of Caerbannog
2           Bridgekeeper  Bridge of Death


MESSAGEPART
----------------------------------------------
MESSAGEID   PARTNO        CONTENT
----------------------------------------------
1           0             (BLOB)
1           1             (BLOB)
3           0             (BLOB)

(MESSAGEPART有一个复合 PRIMARY KEY("MESSAGEID", "PARTNO"))

预期输出

根据上面的数据，我应该得到这样的结果:

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
2           0

很明显，我需要在 MESSAGE表上执行左连接，但是如何返回 0的计数，因为其中来自 MESSAGEPART的连接列是 NULL？我试过以下方法:

逻辑

我试过了

SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

然而，这回报

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
2           1

我也试过

SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY mp.MESSAGEID;

但现在又回来了

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
1

我做错了什么？

97366 次浏览
小开
最佳答案

这样如何:

SELECT m.MESSAGEID, sum((case when mp.messageid is not null then 1 else 0 end)) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

函数将对每一行进行计数，即使它的值为 null。使用 SUM ()和 CASE，只能计算非空值。

编辑: 摘自顶部评论的一个更简单的版本:

SELECT m.MESSAGEID, COUNT(mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

希望能帮上忙。

小开

我认为，在加入之前，你需要在你的桌子上数数。试试这个:

   SELECT m.MessageId
, COALESCE(c, 0) as myCount
FROM MESSAGE m
LEFT JOIN (SELECT MESSAGEID
, count(*) c
FROM MESSAGEPART
GROUP BY MESSAGEID) mp
ON mp.MESSAGEID = m.MESSAGEID
小开

不要忘记使用 很明显，以防您将左连接多个表:

SELECT m.MESSAGEID, COUNT(DISTINCT mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
小开

根据匹配的列，返回一个数字作为两个表之间匹配元素的总和

在我的例子中，我需要从一个特定列和两个不同的表中返回一个总数来表示匹配项的数量/计数。

例如，我有两个单独的表，每个表都有一个 PhoneNumber 列。在这两张表之间，我想知道有多少匹配的列。

参考资料: https://www.guru99.com/joins.html

使用上面相同的表名，它看起来是这样的:

SELECT COUNT(DISTINCT m.MESSAGEID) AS COUNT FROM MESSAGE m, MESSAGEPART mp
where mp.MESSAGEID = m.MESSAGEID;

提交答案