过来看看，一定给分！^_^
Col1 Col2 Col3
BD1VLabel4
BD1VBATT2
BD1VODD2
BD1VHDD1
BD1VLCD1
BD1WHDD3
BD1WRAM3
BD1WTP CABLE1
BD1WLCD1
BD1WLabel1
BL3LCD CABLE7
BL3LABEL6
BL3LCD5
BL3RAM1
BL3DLabel4
我要的结果是每个Col1的前三大Col3，应该是这样
BD1VBATT2
BD1VODD2
BD1VHDD1
BD1WHDD3
BD1WRAM3
BD1WTP CABLE1
BL3LCD CABLE7
BL3LABEL6
BL3LCD5
BL3DLabel4
高手帮忙解决
[解决办法]
Create Table TEST
(Col1Varchar(10),
Col2Varchar(10),
Col3Int)
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
/*
Col1Col2Col3
BD1VLabel4
BD1VBATT2
BD1VODD2
BD1WHDD3
BD1WRAM3
BD1WLabel1
BL3LCD CABLE7
BL3LABEL6
BL3LCD5
BL3DLabel4
*/
[解决办法]
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)
insert into tb(Col1,Col2,Col3) values( 'BL3 ', 'RAM ',1)
insert into tb(Col1,Col2,Col3) values( 'BL3D ', 'Label ',4)
select id=identity(int,1,1),px=(select count(1) from tb where col1=a.col1 and col3 > a.col3) + 1 , * into test from tb a
order by col1,col3 desc
Select col1,col2,col3 From test a
Where (Select Count(*) From test Where Col1 = A.Col1 And id < A.id) < 3
Order By Col1, Col3 desc
drop table tb,test
col1 col2 col3
---------- ---------- -----------
BD1V Label 4
BD1V BATT 2
BD1V ODD 2
BD1W HDD 3
BD1W RAM 3
BD1W TP CABLE 1
BL3 LCD CABLE 7
BL3 LABEL 6
BL3 LCD 5
BL3D Label 4
（所影响的行数为 10 行）
