如何在sql中选择超过2列的数量

Can someone suggest me a query to get combined count from 2 columns. My specific requirement is as following:

有人可以建议我查询从2列获得组合计数。我的具体要求如下:

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

I want the count of 1 whether it belong to A or B and the permission is 'accept'. For the above example I need the output as 5

我希望计数为1,无论它属于A还是B,权限是'接受'。对于上面的例子,我需要输出为5

4 个解决方案

#1

You can do this by denormalizing first the data using UNION ALL and then use COUNT to achieve the desired result:

您可以通过使用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

RESULT

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

#2

SELECT COUNT(*) FROM table WHERE (A=1 OR B=1) AND Permission LIKE 'accept'

You mean this?

你是这个意思?

#3

As you said A or B, I think when you have 1 in A And in B it means 1 count; So I suggest this edited query?

正如你所说的A或B,我认为当你在A中有1而在B中它意味着1计数;所以我建议这个编辑过的查询?

SELECT M, COUNT(*) As C 
FROM (
    SELECT A as M, Permission
    FROM yourTable
    UNION ALL
    SELECT B, Permission
    FROM yourTable 
    WHERE A <> B) Dt
WHERE Permission = 'accept'
GROUP BY M

#4

I got the answer and changed it to work for postgre :

我得到了答案并将其更改为postgre工作:

WITH SampleData(auid, buid, 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.col,   COUNT(*)as count 
 FROM (
  SELECT  auid as col, permission  FROM SampleData
  UNION ALL
  SELECT  buid as col, permission  FROM SampleData
 ) t
 WHERE permission = 'accept'
 GROUP BY t.col

Result

col          count
-----------  -----------
1            5
2            3
3            3
4            2
6            1

#1