SQL语句

来源：互联网发布：乐乎编辑：程序博客网时间：2024/06/16 08:56

mysql:

给指定日期加一天用date_add函数 INterval 1 day

select *  from a,b where  where a.company like '%张%' and a.id=b.company_id   and otime>='2014-10-31' and otime<date_add('2014-11-3', INTERVAL 1 day)   order by b.otime limit 0,1000

如何查询上一条数据和下一条数据，这种写法为了防止：当你点击当前这一条数据7829时，如果正在这时别人删除了这条数据7829，如何给用户显示上一条数据和下一条数据：

 (select '00' tag, id,title,instructions,pic, picsmall from a where id=7829)UNION ALL(select '01' tag, id,title,instructions,pic, picsmall from  a where id > 7829 limit 1)       --  <!-- 下一条 --> UNION ALL(select '02' tag,  id,title,instructions,pic, picsmall from a where id < 7829 order by id DESC limit 1)    -- <!-- 上一条  -->

以上做法似乎是行不通的。可以试试以下方法：

  select  vtb.tag,a.id,a.title,a.instructions,a.pic,a.pic_small picsmall      from  (select '00' tag,7829 vid) vtb left join  a on vtb.vid = tb_mito.id     UNION ALL     select  vtb.tag,a.id,a.title,a.instructions,a.pic,a.pic_small picsmall     from  (select '01' tag,(select id from tb_mito where id >7829 limit 1) vid) vtb left join  a on vtb.vid = a.id     UNION ALL     select  vtb.tag,a.id,a.title,a.instructions,a.pic,a.pic_small picsmall     from  (select '02' tag,(select id from a where id < 7829 order by id DESC limit 1) vid) vtb left join  a on vtb.vid = tb_mito.id

0 0