从表的多个列中选择不同的值及其计数 [英] Selecting distinct values from multiple column of a table with their count

问题描述

我想从同一表的多个列中选择不同的值及其计数 例子

i want to to select distinct values from multiple columns of same table with their count example

输出应该是这样的

推荐答案

将所有列值都保存到一行并找到计数

Get all column values to one row and find the count

SQL SERVER

;WITH CTE AS
(
   SELECT COL1 Name
   FROM YOURTABLE
   UNION ALL
   SELECT COL2
   FROM YOURTABLE
   UNION ALL
   SELECT COL3
   FROM YOURTABLE
   UNION ALL
   SELECT COL4
   FROM YOURTABLE
   UNION ALL
   SELECT COL6
   FROM YOURTABLE
   UNION ALL
   SELECT COL7
   FROM YOURTABLE
)
SELECT DISTINCT Name,COUNT(Name) OVER(PARTITION BY Name) [COUNT]
FROM CTE 

MYSQL

SELECT Name,COUNT(*) [COUNT]
(
   SELECT COL1 Name
   FROM #TEMP
   UNION ALL
   SELECT COL2
   FROM #TEMP  
   UNION ALL
   SELECT COL3
   FROM #TEMP  
   UNION ALL
   SELECT COL4
   FROM #TEMP  
   UNION ALL
   SELECT COL6
   FROM #TEMP  
   UNION ALL
   SELECT COL7
   FROM #TEMP      
)TAB   
GROUP BY Name

这篇关于从表的多个列中选择不同的值及其计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！