计算左连接中加入的行数
计算左连接中加入的行数
我试图在SQL中编写一个聚合查询,返回与表中给定记录关联的所有记录的计数;如果没有记录与给定记录关联,则该记录的结果应为0:\n
数据
\n我的数据库看起来像这样(不幸的是,我无法更改结构):\nMESSAGE\n----------------------------------------------\nMESSAGEID SENDER SUBJECT\n----------------------------------------------\n1 Tim Rabbit of Caerbannog\n2 Bridgekeeper Bridge of Death\nMESSAGEPART\n----------------------------------------------\nMESSAGEID PARTNO CONTENT\n----------------------------------------------\n1 0 (BLOB)\n1 1 (BLOB)\n3 0 (BLOB)\n
\n(MESSAGEPART
具有复合主键\"MESSAGEID\",\"PARTNO\"
)\n
期望输出
\n根据上述数据,我应该得到类似于以下内容:\nMESSAGEID COUNT(*)\n-----------------------------------------------\n1 2\n2 0\n
\n很明显,我需要在MESSAGE
表上执行左连接,但是如何在从MESSAGEPART
加入的列为NULL
的行中返回计数为0
呢?我尝试了以下方法:\n
逻辑
\n我尝试了以下方法:\n
SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID;
\n但是,这返回了:\nMESSAGEID COUNT(*)\n-----------------------------------------------\n1 2\n2 1\n
\n我还尝试了以下方法:\n
SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY mp.MESSAGEID;
\n但是这返回了:\nMESSAGEID COUNT(*)\n-----------------------------------------------\n1 2\n 1\n
\n我在这里做错了什么?
在使用左连接(LEFT JOIN)时,需要注意在查询中使用DISTINCT关键字,以防止出现重复计算的情况。这是因为左连接会将左表中的所有行与右表中匹配的行进行连接,可能会导致结果中出现重复的行。为了避免重复计算,可以使用DISTINCT关键字来确保结果集中的行是唯一的。
解决方法如下所示:
SELECT m.MESSAGEID, COUNT(DISTINCT mp.MESSAGEID) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID;
上述代码中,通过在COUNT函数中使用DISTINCT关键字,可以确保只计算唯一的mp.MESSAGEID的数量。这样就可以避免因为左连接而导致的重复计算问题。
通过以上方法,在使用左连接时可以准确地计算出连接后的行数。
对于左连接(left join)中的行数进行计数的问题,出现的原因是需要在连接之前对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
虽然这个解决方法比上面的解决方法更复杂,但是我必须将这个查询嵌套在另一个查询中,所以你的解决方法也是有帮助的;感谢你的帮助。
无论这个方法是否有效率,从知识的角度来说,它非常有用,尤其是在现在这个时候!
Counting number of joined rows in left join这个问题的出现的原因是,使用COUNT()函数进行计数时,会计算所有行,即使它们是空值。然而,有时我们只想计算非空值的数量。解决这个问题的方法是使用SUM()函数和CASE语句,通过判断非空值进行计数。
以下是一种解决该问题的SQL查询语句:
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;
另外,还有一种更简化的版本:
SELECT m.MESSAGEID, COUNT(mp.MESSAGEID) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID;
这种简化的版本使用了更简单的count(mp.messageid)
语句进行计数,不再使用sum(case....end)
的形式。其中,Count(*)
会计算所有行,包括空值,而count(col_name)
只会计算非空值。
对于这个问题的解决方法,有一位用户指出了一个注意事项。如果想要在查询中添加WHERE子句,需要将它放在GROUP BY
之前,例如:WHERE (mp.PARTNO = 1 OR mp.PARTNO IS NULL)
。
然而,还有一位用户提出了一个问题,即如果不想使用GROUP BY
怎么办。