如何取每个品种当天的不同品种的2条数据

[lang11zi]

table

id wzdm dwmc rq sj
1 A1101 a 2006-2-27 19:00:00
2 A1101 a 2006-2-27 19:00:00
3 A1102 a 2006-2-27 19:00:00
4 A1102 a 2006-2-27 19:00:00
5 A1103 a 2006-2-27 19:00:00
6 A1103 a 2006-2-27 19:00:00
7 A1101 b 2006-2-27 19:00:00
8 A1101 b 2006-2-27 19:00:00
9 A1102 b 2006-2-27 19:00:00

结果
id wzdm dwmc rq sj
1 A1101 a 2006-2-27 19:00:00
3 A1102 a 2006-2-27 19:00:00
7 A1101 b 2006-2-27 19:00:00
8 A1102 b 2006-2-27 19:00:00

就是每个dwmc选出不重复的两条wzdm

[lsqkeke]

declare @t table(id int,wzdm varchar(10) , dwmc varchar(5),rq varchar(20),sj varchar(20))
insert @t
select 1,'A1101', 'a', '2006-2-27', '19:00:00' union all
select 2,'A1101', 'a', '2006-2-27', '19:00:00' union all
select 3,'A1102', 'a', '2006-2-27', '19:00:00' union all
select 4,'A1102', 'a', '2006-2-27', '19:00:00' union all
select 5,'A1103', 'a', '2006-2-27', '19:00:00' union all
select 6,'A1103', 'a', '2006-2-27', '19:00:00' union all
select 7,'A1101', 'b', '2006-2-27', '19:00:00' union all
select 8,'A1101', 'b', '2006-2-27', '19:00:00' union all
select 9,'A1102', 'b', '2006-2-27', '19:00:00'

select * from
(select id=min(id),wzdm,dwmc,rq=min(rq),sj=min(sj) from @t group by wzdm,dwmc)t
where id in(select top 2 id from (select id=min(id),wzdm,dwmc from @t group by wzdm,dwmc)g
where g.dwmc=t.dwmc )


结果：
id wzdm dwmc rq sj
1 A1101 a 2006-2-27 19:00:00
3 A1102 a 2006-2-27 19:00:00
7 A1101 b 2006-2-27 19:00:00
9 A1102 b 2006-2-27 19:00:00

[lsqkeke]

由于是当天，也可这样写：
select * from
(select id=min(id),wzdm,dwmc,rq,sj from @t group by wzdm,dwmc,rq,sj)t
where id in(select top 2 id from (select id=min(id),wzdm,dwmc from @t group by wzdm,dwmc)g where g.dwmc=t.dwmc )

[lk919]

是随机的吗