有一张表a
编号 金额
1 100
2 2000
4 600
7 6000
9 1000
……
我想查出这样的数据
金额>1000且排名前15的为一等奖
金额>500且排名在前20(不包括一等奖获得者)
现在一等奖的好筛选,如果想查二等奖的用sql怎么实现呢?
编号 金额
1 100
2 2000
4 600
7 6000
9 1000
……
我想查出这样的数据
金额>1000且排名前15的为一等奖
金额>500且排名在前20(不包括一等奖获得者)
现在一等奖的好筛选,如果想查二等奖的用sql怎么实现呢?
where 金额>500 and 编号 not in (select top 15 编号 from a where 金额>1000 order by 金额 desc)
order by 金额 desc
insert T select 1, 100
union all select 2, 2000
union all select 4, 600
union all select 7, 6000
union all select 9, 1000select top 15 col1='一等奖', * from T where [Money]>1000 order by [Money] descselect top 20 col1='二等奖', * from T where [Money]>500 and [Money]<=1000 order by [Money] desc
樓上的,要是>1000的不止15個,第二個語句會不會有問題呢?
--
因為 [Money]>500 and [Money]<=1000 所以不會出現大於1000的記錄
where 編號 in
(select top 35 編號 from T where [Money]>500 order by [Money] desc) order by [Money]
from a aa
where je > 1000
and ( select count(1) from a where je >= aa.je ) <= 15
select top 15 *
from a
where je > 1000
order by je asc2.
select *
from t
where je > 500
and id not in
(
select top 15 id
from a
where je > 1000
order by je asc
)
and ( select count(1) from a where je >= aa.je ) <= 20
or select top 20 *
from t
where je > 500
and id not in
(
select top 15 id
from a
where je > 1000
order by je asc
)
order by je asc
select top 20 * from a where 金额>500 and 金额<=1000 order by 金额