我有一个表,所有值都是0或1。
a | b | c |
---|---|---|
1 | 0 | 0 |
1 | 1 | 0 |
0 | 1 | 0 |
1 | 1 | 1 |
而我想要这个表:
a | b | c | |
---|---|---|---|
a | 3 | 2 | 1 |
b | 2 | 3 | 1 |
c | 1 | 1 | 1 |
最后这个表回答了关于{raw}和{col}都为1的行数的问题。例如,在第一个表中,有2行满足a = b = 1,所以cell(a,b) = 2。
我有一个查询语句对于大型表格不太合适。有没有可能让它更简单?
SELECT
'a' AS ' ',
SUM(a) AS a,
(SELECT SUM(b) FROM tab WHERE a = 1) AS b,
(SELECT SUM(c) FROM tab WHERE a = 1) AS c
FROM
tab
UNION
SELECT
'b',
(SELECT SUM(a) FROM tab WHERE b = 1),
SUM(b),
(SELECT SUM(c) FROM tab WHERE b = 1)
FROM
tab
UNION
SELECT
'c',
(SELECT SUM(a) FROM tab WHERE c = 1),
(SELECT SUM(b) FROM tab WHERE c = 1),
SUM(c)
FROM
tab