过来看看，一定给分！解决方案

过来看看，一定给分！^_^
Col1           Col2           Col3
BD1V Label 4
BD1V BATT 2
BD1V ODD 2
BD1V HDD 1
BD1V LCD 1
BD1W HDD 3
BD1W RAM 3
BD1W TP   CABLE 1
BD1W LCD 1
BD1W Label 1
BL3 LCD   CABLE 7
BL3 LABEL 6
BL3 LCD 5
BL3 RAM 1
BL3D Label 4

我要的结果是每个Col1的前三大Col3，应该是这样
BD1V BATT 2
BD1V ODD 2
BD1V HDD 1
BD1W HDD 3
BD1W RAM 3
BD1W TP   CABLE 1
BL3 LCD   CABLE 7
BL3 LABEL 6
BL3 LCD 5
BL3D Label 4

高手帮忙解决




------解决方案--------------------
Create Table TEST
(Col1 Varchar(10),
Col2 Varchar(10),
Col3 Int)

Insert TEST Select 'BD1V ', 'Label ', 4
Union All Select 'BD1V ', 'BATT ', 2
Union All Select 'BD1V ', 'ODD ', 2
Union All Select 'BD1V ', 'HDD ', 1
Union All Select 'BD1V ', 'LCD ', 1
Union All Select 'BD1W ', 'HDD ', 3
Union All Select 'BD1W ', 'RAM ', 3
Union All Select 'BD1W ', 'TP CABLE ', 1
Union All Select 'BD1W ', 'LCD ', 1
Union All Select 'BD1W ', 'Label ', 1
Union All Select 'BL3 ', 'LCD CABLE ', 7
Union All Select 'BL3 ', 'LABEL ', 6
Union All Select 'BL3 ', 'LCD ', 5
Union All Select 'BL3 ', 'RAM ', 1
Union All Select 'BL3D ', 'Label ', 4
GO
Select ID = Identity(Int, 1, 1), * Into #T From TEST Order By Col1, Col3

--Select * From #T
--方法一：
Select Col1, Col2, Col3 From #T A
Where (Select Count(*) From #T Where Col1 = A.Col1 And ID > A.ID) < 3
Order By Col1, ID Desc
--方法二：
Select Col1, Col2, Col3 From #T A
Where Exists (Select Count(*) From #T Where Col1 = A.Col1 And ID > A.ID Having Count(*) < 3)
Order By Col1, ID Desc
--方法三：
Select Col1, Col2, Col3 From #T A
Where ID In (Select TOP 3 ID From #T Where Col1 = A.Col1 Order By ID Desc)
Order By Col1, ID Desc

Drop Table #T
GO
Drop Table TEST
/*
Col1 Col2 Col3
BD1V Label 4
BD1V BATT 2
BD1V ODD 2
BD1W HDD 3
BD1W RAM 3
BD1W Label 1
BL3 LCD CABLE 7
BL3 LABEL 6
BL3 LCD 5
BL3D Label 4
*/
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb
(
Col1 varchar(10),
Col2 varchar(10),
Col3 int
)

insert into tb(Col1,Col2,Col3) values( 'BD1V ', 'Label ',4)
insert into tb(Col1,Col2,Col3) values( 'BD1V ', 'BATT ',2)
insert into tb(Col1,Col2,Col3) values( 'BD1V ', 'ODD ',2)
insert into tb(Col1,Col2,Col3) values( 'BD1V ', 'HDD ',1)
insert into tb(Col1,Col2,Col3) values( 'BD1V ', 'LCD ',1)
insert into tb(Col1,Col2,Col3) values( 'BD1W ', 'HDD ',3)
insert into tb(Col1,Col2,Col3) values( 'BD1W ', 'RAM ',3)
insert into tb(Col1,Col2,Col3) values( 'BD1W ', 'TP CABLE ',1)
insert into tb(Col1,Col2,Col3) values( 'BD1W ', 'LCD ',1)
insert into tb(Col1,Col2,Col3) values( 'BD1W ', 'Label ',1)
insert into tb(Col1,Col2,Col3) values( 'BL3 ', 'LCD CABLE ',7)
insert into tb(Col1,Col2,Col3) values( 'BL3 ', 'LABEL ',6)
insert into tb(Col1,Col2,Col3) values( 'BL3 ', 'LCD ',5)