mysql - 在其他分组中添加count = 0的新行

我有这张桌子：

||ID || Group || Status
|| 1 ||  AAAA ||  aaaaa
|| 2 ||  AAAA ||  bbbbb
|| 3 ||  BBBB || aaaaa
|| 4 ||  BBBB ||  bbbbb
|| 5 ||  BBBB ||  aaaaa
|| 6 ||  BBBB ||  ccccc
|| 7 ||  BBBB ||  ddddd

我想作为我的查询结果：

|| Status || Group || Count(Status)
|| aaaaa || AAAA  || 1
|| bbbbb || AAAA  || 1
|| ccccc || AAAA  || 0
|| ddddd || AAAA  || 0
|| aaaaa || BBBB  || 2
|| bbbbb || BBBB  || 1
|| ccccc || BBBB  || 1
|| ddddd || BBBB  || 1

我试过：

select status, count(status), group
    from TABLE
    group by status, group

但此代码无法添加count=0的行

最佳答案

首先，要生成status和group的所有组合。您可以使用CROSS JOIN来完成此操作。然后，在你的桌子上做一个LEFT JOIN来得到COUNT
SQL Fiddle

WITH Cte AS(
    SELECT
        a.[group], b.[status]
    FROM (
        SELECT DISTINCT [group] FROM YourTable
    )a
    CROSS JOIN (
        SELECT DISTINCT [status] FROM YourTable
    )b
)
SELECT
    c.[status],
    c.[group],
    ISNULL(COUNT(t.Id), 0) AS StatusCount
FROM Cte c
LEFT JOIN YourTable t
    ON t.[status] = c.status
    AND t.[group] = c.[group]
GROUP BY c.[status], c.[group]

使用子查询：
SQL Fiddle

SELECT
    c.[status],
    c.[group],
    ISNULL(COUNT(t.Id), 0) AS StatusCount
FROM (
    SELECT * FROM(
        SELECT
            a.[group], b.[status]
        FROM (
            SELECT DISTINCT [group] FROM YourTable
        )a
        CROSS JOIN (
            SELECT DISTINCT [status] FROM YourTable
        )b
    )t
) c
LEFT JOIN YourTable t
    ON t.[status] = c.status
    AND t.[group] = c.[group]
GROUP BY c.[status], c.[group]