#业务需求:
当前有一个文章列表按照时间排序,点击一条查看详情时,详情页面要显示当前文章的前一条和后一条数据。
由于功能简单,两条数据有共通的地方,想用一条sql查出两条数据,添加一个orderposition标识前一条(front)、后一条(back)!
##功能实现sql(Oracle):
```sql
with t as (
select t.,rownum as rn from (
select from tablename t
where 1=1 —这里写查询条件
order by t.createtime desc
) t
)
select t.*,'back' as orderposition from t where t.rn=(select t.rn+1 from t where t.id = #{当前文章的id})
union all
select t.*,'front' as orderposition from t where t.rn=(select t.rn-1 from t where t.id = #{当前文章的id})
```
##功能实现sql(Mysql):
```sql
(select a.*,'front' orderposition from article a where edit_date >= (select edit_date from article where id = 15) order by a.edit_date ,id desc limit 1,1 )
union all
(select a.*,'back' orderposition from article a where edit_date <= (select edit_date from article where id = 15) order by a.edit_date desc,id limit 1,1);
```
#总结:
也没什么总结的,就这样了,好好学习技术,成为大牛!