你的语句
Select top 6 PId,PName,PPrice from Product order by PClick desc
只会得到6条记录如果想得到前6名
Select PId,PName,PPrice
from Product a
where (select count(*) from Product
where PClick>a.PClick)<6
Select top 6 PId,PName,PPrice from Product order by PClick desc
只会得到6条记录如果想得到前6名
Select PId,PName,PPrice
from Product a
where (select count(*) from Product
where PClick>a.PClick)<6
goif exists(select name from sysobjects where name='tblA' and xtype='U')
drop table tblAcreate table tblA(
pID int identity(1,1) not null primary key,
PName varchar(10) not null,
PPrice numeric(8,2),
pClick int not null
)
goinsert tblA(PName, PPrice, pClick) values('aaa', 12.3, 10)
insert tblA(PName, PPrice, pClick) values('bbb', 13.3, 9)
insert tblA(PName, PPrice, pClick) values('ccc', 14.3, 8)
insert tblA(PName, PPrice, pClick) values('ddd', 11.3, 7)
insert tblA(PName, PPrice, pClick) values('eee', 19.3, 6)
insert tblA(PName, PPrice, pClick) values('fff', 16.3, 6)
insert tblA(PName, PPrice, pClick) values('ggg', 12.5, 6)
insert tblA(PName, PPrice, pClick) values('hhh', 11.9, 6)
insert tblA(PName, PPrice, pClick) values('iii', 10.3, 5)
insert tblA(PName, PPrice, pClick) values('jjj', 17.7, 4)
insert tblA(PName, PPrice, pClick) values('kkk', 15.9, 4)
goSelect top 6 PId,PName,PPrice, pClick from tblA order by PClick descPId PName PPrice pClick
----------- ---------- ---------- -----------
1 aaa 12.30 10
2 bbb 13.30 9
3 ccc 14.30 8
4 ddd 11.30 7
7 ggg 12.50 6
6 fff 16.30 6(所影响的行数为 6 行)