寻找GROUP BY的细微变化 [英] looking for a slight variant of GROUP BY

问题描述

我有一张这样的桌子:

id attr
1  A
2  A
3  C
4  C
5  D
6  F

我要计算所有A的B(但不包括C的D，等等.)请注意，我的表中的B为零.

I want a count of all the A's B's (but not the C's D's, etc..) Note that my table has zero B's.

所以我想要这样的命令:

So I want a command like this:

SELECT count(attr=A, attr=B) FROM table;

或者这个:

SELECT count(*) FROM table GROUP_BY attr IN (A, B);

并获得:

attr count
A    2
B    0

我的实际表有大约一千个attr.我想对其中的一百多个进行"group-by-ish"操作.重要的是，我必须将某些attr的计数设为零，并使attr与该计数相关联.

My actual table has about a thousand attrs. I want to do a group_by-ish thing on maybe a hundred or so of them. It's important that i get the count of zero for certain attrs and that i can correlate the attr to the count.

我知道这是一个基本问题，如果以前曾问过这个问题，我并不感到惊讶.我搜索了..但是我还是道歉.

I know this is a basic question and I'm not surprised if this has been asked before. I searched.. But my apologies anyway..

推荐答案

SELECT T.attr,
       COUNT(Y.id)
FROM   (SELECT 'A' AS attr
        UNION ALL
        SELECT 'B') AS T
       LEFT JOIN YourTable Y
         ON Y.attr = T.attr
GROUP  BY T.attr  

这篇关于寻找GROUP BY的细微变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！