SQL 计算左连接中连接的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20119901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting number of joined rows in left join
提问by errantlinguist
I'm trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should be 0:
我正在尝试用 SQL 编写一个聚合查询，它返回连接到表中给定记录的所有记录的计数；如果没有记录加入给定的记录，那么该记录的结果应该是0：
Data
数据
My database looks like this (I'm not able to change the structure, unfortunately):
我的数据库看起来像这样（不幸的是，我无法更改结构）：
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)
(MESSAGEPARThas a composite PRIMARY KEY("MESSAGEID", "PARTNO"))
（MESSAGEPART有复合PRIMARY KEY("MESSAGEID", "PARTNO")）
Desired output
期望输出
Given the data above I should get something like this:
鉴于上面的数据，我应该得到这样的东西：
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
2 0
It seems obvious that I need to do a left join on the MESSAGEtable, but how do I return a count of 0for rows where the joined columns from MESSAGEPARTare NULL? I've tried the following:
它似乎很明显，我需要做的左连接的MESSAGE表，但我怎么回的计数0为行，其中联接的列从MESSAGEPART是NULL？我尝试了以下方法：
Logic
逻辑
I've tried
我试过了
SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
However, this returns
然而，这返回
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
2 1
I've also tried
我也试过
SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY mp.MESSAGEID;
but this returns
但这会返回
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
1
What am I doing wrong here?
我在这里做错了什么？
回答by Mark J. Bobak
How about something like this:
这样的事情怎么样：
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;
The COUNT() function will count every row, even if it has null. Using SUM() and CASE, you can count only non-null values.
COUNT() 函数将对每一行进行计数，即使它为空。使用 SUM() 和 CASE，您只能计算非空值。
EDIT: A simpler version taken from the top comment:
编辑：取自顶级评论的更简单版本：
SELECT m.MESSAGEID, COUNT(mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
Hope that helps.
希望有帮助。
回答by oerkelens
You firstwant to count in your messaepart table before joining, i think. Try this:
我想，在加入之前，您首先要在您的messaepart 表中计数。尝试这个：
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
回答by user2148906
Don't forget to use DISTINCTin case you will LEFT JOIN more than one table:
不要忘记使用DISTINCT以防您将 LEFT JOIN 超过一张表：
SELECT m.MESSAGEID, COUNT(DISTINCT mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
