34,588
社区成员
发帖
与我相关
我的任务
分享
select sum(temp) from test
select temp from test order by temp asc limit 10
select temp from test order by temp desc limit 10
比如有100行温度
我想取中间的80个温度
怎么取呢 ,我只会取上面10个和下面10个
select * from xxx as a
where a.id not in (
select top 10 b.id from xxx as b order by b.temp ASC
)
and a.id not in (
select top 10 c.id from xxx as c order by c.temp DESC
)
_sqlserver不支持limit_
sqlserver中实现你的目的有一个简单的方式,取差集
select temp from test --从中取下面两行中没有的行
except
select temp from (select top 10 temp from test order by temp )t --取温度最低的前10行
except
select temp from (select top 10 temp from test order by temp desc)t --取温度最高的前10行
取中间的需要给数据排序,有几个可用的窗口函数 percent_rank, row_number 等,
比如:
select *
from
(select tmp, percent_rank() over(order by tmp) pct
from test)
where pct between 你的范围