SQL Server 计算表的每一列中不同值的数量 [英] SQL Server count number of distinct values in each column of a table

问题描述

我有一个大约 150 列的表格.我想找到每一列的 count(distinct(colName)) 但我想知道是否有办法在不实际输入每个列名的情况下这样做.

I have a table with ~150 columns. I'd like to find the count(distinct(colName)) for each column but am wondering if there's a way to do so without actually typing out each column name.

理想情况下我会使用 count(distinct(*)) 但这不起作用.

Ideally I would use count(distinct(*)) but this doesn't work.

还有其他建议吗?

如果这是我的桌子:

  id         col1         col2        col3      ...
  01         10001       west         north  
  02         10001       west         south  
  03         10002       east         south  
  04         10002       west         north  
  05         10001       east         south  
  06         10003       west         north 

我正在寻找这个输出

count(distinct(id))   count(distinct(col1))    count(distinct(col2))   count(distinct(col3))
       6                       3                    2                      2

推荐答案

您可以这样做:

DECLARE @query varchar(max)
    SELECT @query = 
    'SELECT ' + SUBSTRING((SELECT ',' +'COUNT(DISTINCT(' + column_name + ')) 
             As ' + column_name + ' '  
             FROM information_schema.columns
             WHERE 
             table_name = 'table_name'
             for xml path('')),2,200000)  +  'FROM table_name'

PRINT(@query)

这篇关于SQL Server 计算表的每一列中不同值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！