select count(distinct col1) as col1, count(distinct col2) as col2, count(distinct col3) sa col3, count(distinct col4) as col4 from table1
------------------------------------ -- Author: aimee -- Version:V1.001 -- Date:2009-12-24 14:23:11 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(col1 nvarchar(1),col2 nvarchar(1),col3 nvarchar(1),col4 nvarchar(1)) Go Insert into ta select 'z','a','c','e' union all select 'x','a','g','e' union all select 'w','b','f','e' Go --Start Select count(distinct col1),count(distinct col2), count(distinct col3),count(distinct col4) from ta --Result: /* ----------- ----------- ----------- ----------- 3 2 3 1(所影响的行数为 1 行) */ --End
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1)) insert [tb] select 'z','a','c','e' union all select 'x','a','g','e' union all select 'w','b','f','e'
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1)) insert [tb] select 'z','a','c','e' union all select 'x','a','g','e' union all select 'w','b','f','e'select count(distinct [col1]) as [col1], count(distinct [col2]) as [col2], count(distinct [col3]) as [col3], count(distinct [col4]) as [col4] from [tb]----------------------- 3 2 3 1
select count(col1) over(partition by col1) as col1 ,count(col2) over(partition by col2) as col2 ,count(col3) over(partition by col3) as col3 ,count(col4) over(partition by col4) as col4 from Talbe
--来个动态的 --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1)) insert [tb] select 'z','a','c','e' union all select 'x','a','g','e' union all select 'w','b','f','e'declare @sql varchar(8000) select @sql = isnull(@sql+',','')+'count(distinct '+name+') as ['+name+']' from( select name from dbo.syscolumns where id=object_id('tb') ) t print 'select '+@sql+' from tb' exec('select '+@sql+' from tb')---------------------------- 3 2 3 1
count(distinct col2)col2,
count(distinct col3)col3,
count(distinct col4)col4
from tb
count(distinct col2 )col2,
count(distinct col3 )col3,
count(distinct col4 )col4
from tb
count(distinct col2) as col2,
count(distinct col3) sa col3,
count(distinct col4) as col4
from table1
-- Author: aimee
-- Version:V1.001
-- Date:2009-12-24 14:23:11
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(col1 nvarchar(1),col2 nvarchar(1),col3 nvarchar(1),col4 nvarchar(1))
Go
Insert into ta
select 'z','a','c','e' union all
select 'x','a','g','e' union all
select 'w','b','f','e'
Go
--Start
Select count(distinct col1),count(distinct col2), count(distinct col3),count(distinct col4)
from ta
--Result:
/*
----------- ----------- ----------- -----------
3 2 3 1(所影响的行数为 1 行)
*/
--End
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1))
insert [tb]
select 'z','a','c','e' union all
select 'x','a','g','e' union all
select 'w','b','f','e'
---查询---
select count(distinct col1)col1,
count(distinct col2)col2,
count(distinct col3)col3,
count(distinct col4)col4
from tb---结果---
col1 col2 col3 col4
----------- ----------- ----------- -----------
3 2 3 1(所影响的行数为 1 行)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1))
insert [tb]
select 'z','a','c','e' union all
select 'x','a','g','e' union all
select 'w','b','f','e'select count(distinct [col1]) as [col1],
count(distinct [col2]) as [col2],
count(distinct [col3]) as [col3],
count(distinct [col4]) as [col4]
from [tb]-----------------------
3 2 3 1
,count(col2) over(partition by col2) as col2
,count(col3) over(partition by col3) as col3
,count(col4) over(partition by col4) as col4
from Talbe
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1),[col4] varchar(1))
insert [tb]
select 'z','a','c','e' union all
select 'x','a','g','e' union all
select 'w','b','f','e'declare @sql varchar(8000)
select @sql = isnull(@sql+',','')+'count(distinct '+name+') as ['+name+']'
from(
select name from dbo.syscolumns where id=object_id('tb')
) t
print 'select '+@sql+' from tb'
exec('select '+@sql+' from tb')----------------------------
3 2 3 1