我的表如下:ID NAME TYPE
-------------------------------
1 A1 BJ
2 B2 GD
3 B3 BJ
4 B4 GD
5 A6 BJ
6 XX GD
7 A8 BJ
8 X8 GD
我现在要的结果是如下的记录集:ID NAME TYPE
-------------------------------
1 A1 BJ
3 B3 BJ
2 B2 GD
4 B4 GD
就是TYPE='BJ'的2个记录,TYPE='GD'2个记录, TYPE为出现的任何值的2条记录的记录集.排序先是指定条件TYPE的顺序,然后是ID,比如先让BJ,然后是GD 这样指定的条件顺序用一条语句实现.请各位高手帮忙,,谢谢!
-------------------------------
1 A1 BJ
2 B2 GD
3 B3 BJ
4 B4 GD
5 A6 BJ
6 XX GD
7 A8 BJ
8 X8 GD
我现在要的结果是如下的记录集:ID NAME TYPE
-------------------------------
1 A1 BJ
3 B3 BJ
2 B2 GD
4 B4 GD
就是TYPE='BJ'的2个记录,TYPE='GD'2个记录, TYPE为出现的任何值的2条记录的记录集.排序先是指定条件TYPE的顺序,然后是ID,比如先让BJ,然后是GD 这样指定的条件顺序用一条语句实现.请各位高手帮忙,,谢谢!
WHERE ID IN (SELECT TOP 2 ID FROM 表 WHERE TYPE=A.TYPE)
ORDER BY TYPE,ID
SELECT * FROM 表 A WHERE ID IN (SELECT TOP 2 ID FROM 表 WHERE TYPE=A.TYPE) 才排序的.
-------------------------------
insert @t select 1,'A1','BJ'
insert @t select 2, 'B2', 'GD'
insert @t select 3, 'B3' , 'BJ'
insert @t select 4, 'B4' , 'GD'
insert @t select 5, 'A6', 'BJ'
insert @t select 6 , 'XX' , 'GD'
insert @t select 7, 'A8' , 'BJ'
insert @t select 8 , 'X8' , 'GD'select * from @t b where id in (select top 2 id from @t a where a.type=b.type)
order by type,id
----------------------------------------
id name type
1 A1 BJ
3 B3 BJ
2 B2 GD
4 B4 GD
create table #tb(ID int,[NAME] varchar(50),[TYPE] varchar(20))insert into #tb
select 1,'A1','BJ' union all
select 2,'B2','GD' union all
select 3,'B3','BJ' union all
select 4,'B4','GD' union all
select 5,'A6','BJ' union all
select 6,'XX','GD' union all
select 7,'A8','BJ' union all
select 8,'X8','GD'
--测试
with TypeCET as
(
select *,row_number() over(PARTITION BY [type] order by ID,[type]) TypeID
from #tb
)
select ID,[Name],[Type] from TypeCET where TypeID<=2
order by ID
--显示结果
/*
ID NAME TYPE
-------------------------------
1 A1 BJ
3 B3 BJ
2 B2 GD
4 B4 GD
*/
--删除测试环境
drop table #tb
(
select *,row_number() over(PARTITION BY [type] order by [type],ID) TypeID
from #tb
)
select ID,[Name],[Type] from TypeCET where TypeID<=2
order by ID