mssql sqlserver 使用count获取group by 分组后的个数方法


摘要:
下文将分享三种统计分组后行数的方法分享,如下所示:
实验环境:sql server 2008 R2


例:
获取表test中infoA和infoB列值不相同的行数

 create table test
(keyid int identity,
 infoA varchar(30),
 infoB varchar(30))
insert into test 
(infoA,infoB)values
('maomao365.com','sql_blog'),
('A','B'),
('maomao365.com','sql_blog'),
('maomao365.com','sql_blog'),
('test','sql_blog')

---1.使用row_number开窗函数 ,进行群组后,然后获取组内编号为1的行信
select COUNT(1)  from (
select ROW_NUMBER()
over(partition by infoA,infoB order by keyId asc) 
as keyIdnew 
 from test ) as t where t.keyIdNew =1 
go
---2 使用count distinct 统计
select COUNT(distinct infoA+'-'+infoB) 
 from test 
 
--3 使用count和group by 子查询统计
select COUNT(1) from (
select COUNT(1) as q,infoA,infoB from test 
  group by infoA,infoB 
)as t
 
go
truncate table test
drop     table test
mssql_sqlserver_count_统计群组后行数信息

mssql_sqlserver_count_统计群组后行数信息