sql - 在 sqlite 中可以进行此查询吗？

Question

我有一张这样的桌子：

ID A B C
0  x   x
1  x
2  x x
3    x

我想通过查询来获得这个（如果可能的话，SQLite 语法）

A 3
B 2
C 1

我一直在使用 CASE WHEN 子句，像这样

WITH solutions AS (SELECT
    CASE
        WHEN `B` = 'x' THEN 'A'
        WHEN `B` = 'x' THEN 'B'
        WHEN `C` = 'x' THEN 'C'
    END AS 'Solution'
FROM use_cases)
SELECT solution, COUNT(*) AS 'Count'
FROM solutions
GROUP BY Solution ORDER BY COUNT(*) DESC

但如果一行存在多于一列带有“x”的行，它将不起作用，因为 WHEN 评估将在找到第一种情况时停止。

标签： sqlsqlite

Answer 1

使用union all：

select col, count(*)
from (select 'A' as col from t where A = 'x' union all
      select 'B' from t where B = 'x' union all
      select 'C' from t where C = 'x' 
     ) x
group by col
order by count(*) desc;