如何显式显示有count(*)等于0的行?

The query I'm running in DB2

我在DB2中运行的查询。

select  yrb_customer.name, 
        yrb_customer.city, 
        CASE count(*) WHEN 0 THEN 0 ELSE count(*) END as #UniClubs 
from  yrb_member, yrb_customer 
where yrb_member.cid = yrb_customer.cid and yrb_member.club like '%Club%' 
group by yrb_customer.name, yrb_customer.city  order by count(*)

Shows me people which are part of clubs which has the word 'Club' in it, and it shows how many such clubs they are part of (#UniClubs) along with their name and City. However for students who are not part of such a club, I would still like for them to show up but just have 0 instead of them being hidden which is what's happening right now. I cannot get this functionality with count(*). Can somebody shed some light? I can explain further if the above is not clear enough.

向我展示了哪些人是俱乐部的成员，俱乐部里有“俱乐部”这个词，它显示了有多少这样的俱乐部(#UniClubs)，以及他们的名字和城市。但是对于那些不属于这样一个俱乐部的学生来说，我仍然希望他们能够出现，但是只是0而不是隐藏他们，这就是现在正在发生的事情。我不能用count(*)获得这个功能。有人能解释一下吗?如果上面说的不够清楚，我可以进一步解释。

2 个解决方案

#1

You're going to want a LEFT JOIN:

你会想要一个左连接:

SELECT yrb_customer.name, yrb_customer.city,
       COUNT(yrb_member.club) as clubCount
FROM yrb_customer
LEFT JOIN yrb_member
       ON yrb_member.cid = yrb_customer.cid
          AND yrb_member.club LIKE '%Club%
GROUP BY yrb_customer.name, yrb_customer.city
ORDER BY clubCount

Also, if the tuple (yrb_customer.name, yrb_customer.city) is unique (or is supposed to be - are you counting all students with the same name as the same person?), you might get better performance out of the following:

另外，如果tuple (yrb_customer.name, yrb_customer.city)是惟一的(或者应该是——您在计算所有与同一个人同名的学生吗?)，您可能会获得更好的性能:

SELECT yrb_customer.name, yrb_customer.city,
       COALESCE(club.count, 0)
FROM yrb_customer
LEFT JOIN (SELECT cid, COUNT(*) as count
           FROM yrb_member
           WHERE club LIKE '%Club%
           GROUP BY cid) club
       ON club.cid = yrb_customer.cid
ORDER BY club.count

The reason that your original results were being hidden was because in your original query, you have an implicit inner join, which of course requires matching rows. The implicit-join syntax (comma-separated FROM clause) is great for inner (regular) joins, but is terrible for left-joins, which is what you really needed. The use of the implicit-join syntax (and certain types of related filtering in the WHERE clause) is considered deprecated.

隐藏原始结果的原因是，在原始查询中，有一个隐式的内部连接，当然这需要匹配行。关联连接语法(从子句中逗号分隔)对于内部(常规)连接非常有用，但是对于左连接就很糟糕，这正是您真正需要的。使用隐含连接语法(在WHERE子句中有特定类型的相关筛选)被认为是不赞成的。

#2

I'm not familiar with DB2 so I'm taking a stab in the dark, but try this:

我对DB2不熟悉，所以我在黑暗中尝试一下，但是尝试一下:

select  yrb_customer.name, 
        yrb_customer.city, 
        CASE WHEN yrb_member.club like '%Club% THEN count(*) ELSE 0 END as #UniClubs 
from  yrb_member, yrb_customer 
where yrb_member.cid = yrb_customer.cid
group by yrb_customer.name, yrb_customer.city  order by count(*)

Basically you don't want to filter for %Club% in your WHERE clause because you want ALL rows to come back.

基本上，您不希望在WHERE子句中过滤%Club%，因为您希望所有的行都返回。

#1