SELECT 列,COUNT(1)次数 FROM( SELECT a AS 列 FROM TB UNION ALL SELECT b FROM TB UNION ALL SELECT c FROM TB ) AS T
try this:--DROP TABLE TCREATE TABLE T( a INT,b INT, c INT)INSERT INTO T SELECT 1, 2, 3 UNION ALL SELECT 3, 1, 5 UNION ALL SELECT 5, 6, 1 go SELECT COL AS '列', count(*) as '次数' FROM ( SELECT A AS COL FROM T UNION ALL SELECT B FROM T UNION ALL SELECT C FROM T )T group by col order by count(*) desc /* 列 次数 1 3 3 2 5 2 6 1 2 1 */
还有这个:--DROP TABLE TCREATE TABLE T( a INT,b INT, c INT)INSERT INTO T SELECT 1, 2, 3 UNION ALL SELECT 3, 1, 5 UNION ALL SELECT 5, 6, 1 go SELECT COL AS '列', count(*) as '次数' FROM ( SELECT A AS COL FROM T UNION ALL SELECT B FROM T UNION ALL SELECT C FROM T )T group by col order by count(*) desc,列 /* 列 次数 1 3 3 2 5 2 2 1 6 1 */
FROM(
SELECT a AS 列 FROM TB
UNION ALL
SELECT b FROM TB
UNION ALL
SELECT c FROM TB
) AS T
SELECT 1, 2, 3 UNION ALL
SELECT 3, 1, 5 UNION ALL
SELECT 5, 6, 1
go
SELECT COL AS '列', count(*) as '次数'
FROM
(
SELECT A AS COL FROM T
UNION ALL
SELECT B FROM T
UNION ALL
SELECT C FROM T
)T
group by col
order by count(*) desc
/*
列 次数
1 3
3 2
5 2
6 1
2 1
*/
SELECT 1, 2, 3 UNION ALL
SELECT 3, 1, 5 UNION ALL
SELECT 5, 6, 1
go
SELECT COL AS '列', count(*) as '次数'
FROM
(
SELECT A AS COL FROM T
UNION ALL
SELECT B FROM T
UNION ALL
SELECT C FROM T
)T
group by col
order by count(*) desc,列
/*
列 次数
1 3
3 2
5 2
2 1
6 1
*/