帮忙写一个简单的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
