TSQL: 求带聚合的查询

[thinclient]

zcm=注册码, yhm=用户名, dz=地址，bs=表数
由
zcm yhm dz
1 a x
2 a x
3 b y
4 b y
5 b z
查询出如下结果我会
yhm dz bs
a x 2
b y 2
b z 1
但如何查询出如下？
zcm yhm dz bs
1 a x 2
3 b y 2
5 b z 1
在查询结果中，把yhm和dz相同的分为一组，bs是这组中的行数统计
和普通的聚合不同的是，zcm列要出现在结果集中，如果该组bs>1,zcm值取该组中zcm值的任何一个

[「已注销」]

select a.zcm,a.yhm,a.dz,b.bs from table a join (select yhm,dz,count(*) as bs from table group by yhm,dz) b on a.yhm=b.yhm join (select top 1 * from table where yhm=a.yhm and dz=a.dz order by zcm desc) c on a.yhm=c.yhm order by a.zcm

[律己修心]

if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t(zcm int,yhm char,dz char)
insert into #t select 1,'a','x'
union all select 2,'a','x'
union all select 3,'b','y'
union all select 4,'b','y'
union all select 5,'c','z'
union all select 6,'c','y'--add
union all select 7,'c','y'--add

select zcm,t.yhm,t.dz,bs from #t t 
inner join  (select yhm,dz,count(1) as bs from #t group by yhm,dz) z
on t.yhm=z.yhm and t.dz=z.dz
where 1>(select count(1) from #t where dz=t.dz and yhm=t.yhm and zcm<t.zcm)

/*
zcm	yhm	dz	bs
1	a	x	2
3	b	y	2
5	c	z	1
6	c	y	2----add
*/

[lff642]

create table tb(zcm int,yhm varchar(10), dz varchar(10))

insert into tb select 1,'a','x'
insert into tb select 2,'a','x'
insert into tb select 3,'b','y'
insert into tb select 4,'b','y'
insert into tb select 5,'b','z'

select A.zcm,A.yhm,A.dz,B.num from tb A,
      (
        select dz,count(dz) num,min(zcm) zcm  from tb 
          group by dz 
      ) B 
        where A.dz = B.dz and A.zcm = B.zcm


/*
zcm    yhm      dz    num
--------------------------
1	a	x	2
3	b	y	2
5	b	z	1

*/

drop table tb

[octwind]

select dz,count(*) num,min(zcm) zcm from tb group by yhm,dz

[octwind]

select yhm,dz,min(zcm) zcm,count(*) bs from tb group by yhm,dz