计算左连接中连接的行数 [英] Counting number of joined rows in left join

问题描述

我试图在SQL中写一个聚合查询，返回连接到表中给定记录的所有记录的计数;如果没有记录加入到给定记录，则该记录的结果应为 0 ：

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:

我的数据库看起来像这样（不幸的是我不能改变结构）：

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)

（ MESSAGEPART 有一个复合 PRIMARY KEY（MESSAGEID，PARTNO））

给定上面的数据， p>

Given the data above I should get something like this:

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

很明显，我需要在 MESSAGE table，但是对于从 MESSAGEPART 的连接列为<$ c> table的行，如何返回 0 code> NULL ？我尝试了以下操作：

It seems obvious that I need to do a left join on the MESSAGE table, but how do I return a count of 0 for rows where the joined columns from MESSAGEPART are NULL? I've tried the following:

我试过了

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

我在这里做错了什么？

推荐答案

>

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;

COUNT（）函数将计算每一行，即使它有null。使用SUM（）和CASE，您只能计数非空值。

The COUNT() function will count every row, even if it has null. Using SUM() and CASE, you can count only non-null values.

编辑：从顶部注释获取的更简单的版本：

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;

希望有帮助。

这篇关于计算左连接中连接的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！