日期:2014-05-18  浏览次数:20564 次

帮忙写一个简单的SQL查询
假设表test里面有字段ID,HitNum
ID自动增长的,HitNum表示访问量
如果已知ID=100.要求按访问量倒排序,得到当前记录的前一条,后一条记录.能否得到呢?
SQL语句怎么写呢?

------解决方案--------------------
select top 1 * from (select top 2 * from test order by HitNum desc) a order by HitNum
------解决方案--------------------
假设表test里面有字段ID,HitNum
ID自动增长的,HitNum表示访问量
如果已知ID=100.要求按访问量倒排序,得到当前记录的前一条,后一条记录.能否得到呢?
SQL语句怎么写呢?

select * from test where hitnum in
(
select max(HitNum) HitNum from test where hitnum < (select hitnum from test where id = 100)
)
union all
select * from test where hitnum in
(
select min(HitNum) HitNum from test where hitnum > (select hitnum from test where id = 100)
)

------解决方案--------------------
select * from
(select top 1 * from test where ID <> 100 and HitNum <= (select top 1 HitNum from test where ID = 100) order by HitNum desc) a
union all
select * from
(select top 1 * from test where ID <> 100 and HitNum > = (select top 1 HitNum from test where ID = 100) order by HitNum desc) b