mysql - 如何在sql中选择超过2列的计数

Question

有人可以建议我一个查询来获取两列的组合计数。我的具体要求如下:

A     B       Permission
--------------------------
1     2       accept   
2     3       accept   
3     4       accept   
1     6       accept   
1     4       accept   
2     1       accept   
3     1       accept   
4     1       pending   

我想要计数为 1，无论它属于 A 还是 B 并且权限为“accept”。对于上面的示例，我需要输出为 5

Answer 1

您可以通过首先使用 UNION ALL 对数据进行反规范化，然后使用 COUNT 来实现所需的结果:

WITH SampleData(A, B, Permission) AS(
    SELECT 1, 2, 'accept' UNION ALL
    SELECT 2, 3, 'accept' UNION ALL
    SELECT 3, 4, 'accept' UNION ALL
    SELECT 1, 6, 'accept' UNION ALL
    SELECT 1, 4, 'accept' UNION ALL
    SELECT 2, 1, 'accept' UNION ALL
    SELECT 3, 1, 'accept' UNION ALL
    SELECT 4, 1, 'pending'
)
SELECT
    t.ColValue,
    ValueCount = COUNT(*)
FROM (
    SELECT
         Col = 'A', ColValue = A, Permission
    FROM SampleData
    UNION ALL
    SELECT
         Col = 'B', ColValue = B, Permission
    FROM SampleData
) t
WHERE Permission = 'accept'
GROUP BY t.ColValue

结果

ColValue    ValueCount
----------- -----------
1           5
2           3
3           3
4           2
6           1