我正在尝试在多列中进行计数,然后按总和分组,其中相同的数据出现在任何一列中。
Source data table:
P1 P2 P3
-----------
a b
a a a
b c
a b b
b a
我希望它能显示类似这样的内容:
Desired query output:
Total
-------------
a | 6
b | 5
c | 1
SELECT x.f1,Count(x.f1) FROM
(SELECT p1 As F1 FROM table
UNION ALL
SELECT p2 As F1 FROM table
UNION ALL
SELECT p3 As F1 FROM table) x
GROUP BY x.f1
您可以在子查询中使用union all
来合并所有记录,然后在外部查询中count
每个值。
SELECT b.a, COUNT(b.a)
FROM
(
SELECT P1 a
FROM tableName
UNION ALL
SELECT P2 a
FROM tableName
UNION ALL
SELECT P3 a
FROM tableName
) b
GROUP BY b.a