【Transact-SQL】计算整个表中所有值的出现的次数

一个表有3列，5行，那么一共有15个值，现在要计算整个表中所有值在表中出现的次数，不过这里表的列数是不确定的，上面的例子是3列，实际上也有可能是5列、20列，所以解决问题的步骤是这样的：

1、必须知道有多少列，然后构造动态语句，把这些列合并到一列中。

2、然后去重计算出所有的可能值。

3、最后计算每个值在表中出现了多少次。

 

if(OBJECT_ID('dbo.wc') is not null)
	drop table dbo.wc
go


create table wc
	(
	a nvarchar(100),
	b nvarchar(100),
	c nvarchar(100)
	)
	
insert into wc
values('1','2','3'),
      ('a','f','d'),
      ('2','b','c'),
      (null,'c','w'),
      ('3','d',null)


declare @temp table (cn nvarchar(100));

declare @i int = 1;

declare @v varchar(max)='';

declare @column varchar(100)='';

while @i <= (
				select count(*)
				from sys.tables t
				inner join sys.columns c
						on t.object_id =c.object_id 
				where t.name = 'wc'   
             )
begin
    select @column = c.name
	from sys.tables t
	inner join sys.columns c
			on t.object_id =c.object_id 
	where t.name = 'wc'
	      and c.column_id = @i 
	      
	set @i = @i + 1 
    
    set @v = @v + ' select '+ @column + ' from wc union all'

end

select @v = LEFT(@v,len(@v)-LEN('union all'))
--select @v

insert into @temp
exec (@v)

;with a
as
(
select cn
from @temp
where cn is not null
group by cn
)      

select a.cn,
       COUNT(t.cn)
from a
inner join @temp t
        on a.cn = t.cn
group by a.cn
 

 

 

转载于:https://www.cnblogs.com/momogua/archive/2012/08/14/8304609.html