34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
create table tb (class varchar(10),name varchar(7),id varchar(10))
insert into tb
select 'A部门','张三1','001'union all
select 'A部门','张三2','002'union all
select 'A部门','张三3','003'union all
select 'A部门','张三4','004'union all
select 'B部门','李四1','005'union all
select 'B部门','李四2','006'union all
select 'B部门','李四3','007'union all
select 'B部门','李四4','008'
go
select class,count(class)as count from
(
select *,row_number() over(partition by class order by id asc) as rn from tb
)t group by class
--class count
--A部门 4
--B部门 4
select depart, cnt
from (
select depart,
count(name) over(partition by depart) cnt,
row_number() over(partition by depart order by displaynumber) subseq,
row_number() over(order by displaynumber) seq
from tab ) tmp
where subseq = 1
order by seq;