T-SQL如何计算每列的记录 [英] T-SQL How To Count Of Records For Each Column

问题描述

我有一个包含120多个列的表，需要确定使用最少的列。我尝试使用sql查询来执行此操作，但发现T-SQL更为简单。

I have a table with over 120 columns and need to determine which column is used the least. I tried using sql queries to do this, but found T-SQL a bit simpler.

我尝试了以下操作，但每列的计数为0。

I tried the following but my count comes out as 0 for every column.

Declare  data1 Cursor for select column_name 
        from information_schema.columns 
        where table_name = 'repository' 

Declare @mField nvarchar(255)
Declare @count int

Open data1;

fetch next from  data1 into @mField;

set @count = -1;

while @@fetch_status = 0  
    begin
         select   @count = count(@mField)
                    from  repository where tablereference = 
                    'central' and ( @mField!= null )

        print @mField+' ' ; 
                  print @count;
        Fetch next from  data1 into @mField;
    end

close data1;
deallocate data1;

推荐答案

您不能计算这样的值，因为您是仅测试@mField是否为NULL。

You can't count values like this because you are only testing if @mField is NULL. The column name isn't substituted.

COUNT无论如何都会忽略NULL，因此，如果要计算非空值，请执行以下操作：

COUNT ignores NULLs anyway so if you want to count non-null values, do this:

DECLARE @sql varchar(4000)

SET @sql = 'SELECT COUNT(*) AS Total '

SELECT @sql = @sql + ', COUNT(' +  QUOTENAME(column_name) + ') AS ' + QUOTENAME(column_name)
from information_schema.columns 
where table_name = 'repository'

SET @sql = @sql + ' FROM repository'

EXEC (@sql)

此表一次查询所有列

这篇关于T-SQL如何计算每列的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！