用递归不是很简单吗
WITH list (列名,字符,id) AS (
SELECT '列1',列1,id FROM table_test UNION ALL
SELECT '列2',列2,id FROM table_test UNION ALL
SELECT '列3',列3,id FROM table_test UNION ALL
SELECT '列4',列4,id FROM table_test UNION ALL
SELECT '列5',列5,id FROM table_test
),
cte AS (
SELECT * FROM list WHERE id=1
UNION ALL
SELECT list.*
FROM list, cte
WHERE list.列名 = cte.列名
AND list.字符 = cte.字符
AND list.id = cte.id+1
)
SELECT 列名,字符,COUNT(*) 出现次数
FROM cte
GROUP BY 列名,字符
列名 字符 出现次数
---- ---------- -----------
列1 A 1
列2 E 3
列5 K 3
列4 T 4
列3 W 2
WITH list (列名,字符,id) AS (
SELECT '列1',列1,id FROM table_test UNION ALL
SELECT '列2',列2,id FROM table_test UNION ALL
SELECT '列3',列3,id FROM table_test UNION ALL
SELECT '列4',列4,id FROM table_test UNION ALL
SELECT '列5',列5,id FROM table_test
),
cte AS (
SELECT * FROM list WHERE id=1
UNION ALL
SELECT list.*
FROM list, cte
WHERE list.列名 = cte.列名
AND list.字符 = cte.字符
AND list.id = cte.id+1
)
SELECT 列名,字符,COUNT(*) 出现次数
FROM cte
GROUP BY 列名,字符
列名 字符 出现次数
---- ---------- -----------
列1 A 1
列2 E 3
列5 K 3
列4 T 4
列3 W 2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货