我试图在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表上做一个左连接,但是如果从MESSAGEPART的连接列为NULL的行返回0的计数？我尝试过以下操作：
逻辑
我试过了
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
我在这里做错了什么？
解决方法
这样的事情呢
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;
COUNT()函数将对每一行进行计数,即使它具有空值.使用SUM()和CASE,您只能计数非空值.
SELECT m.MESSAGEID,COUNT(mp.MESSAGEID) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID;
希望有帮助.
