效果贴图:
sql查询语句:
StringBuilder sql = new StringBuilder("select * from message model where model.message_id in (");
sql.append(" ( select m1.message_id from message m1 where m1.status=0 and m1.message_id < ? order by m1.message_id desc limit 1 )");
sql.append(" ,?,( select m2.message_id from message m2 where m2.status=0 and m2.message_id > ? order by m2.message_id asc limit 1 ))");
sql.append(" order by model.publish_date desc");
使用上面的查询条件:其中的'?'都是指当前记录的id,
注意:mysql有些版本子查询中是不支持LIMIT & IN/ALL/ANY/SOME:This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery',但是可以巧妙的改一下:即在子查询中可添加个备用的可选条件,即可使用,但是子查询中却不能返回多条记录(如下):
SELECT * FROM reporter r WHERE r.reporter_id IN ((SELECT a.reporter FROM article a ORDER BY a.click_count DESC LIMIT 9),0)
会报错:Subquery returns more than 1 row;
但是如果你在查询外再添加一层即可:
select * from table where id in (select t.id from (select * from table limit 10)as t)
ok这样就可以绕开limit子查询的问题。
效率提升:
select * from table1 where id in (select t.id from (select * from table2 limit 10)as t)
改为
select * from table1 t1, (select * from table2 t2 limit 10)
where t1.id = t2.id
上一条、下一条记录sql改进(子查询不使用limit):
StringBuilder sql = new StringBuilder();
sql.append("select model.article_id,model.title,model.content ");
sql.append(" from article model where model.article_id in (");
sql.append(" ( select max(a1.article_id) from article a1 where a1.status=1 and a1.article_id < ? )");
sql.append(" ,?,( select min(a2.article_id) from article a2 where a2.status=1 and a2.article_id > ? ))");
sql.append(" order by model.publish_date desc");
?:当前记录的id
哎呦 ,我这个脑子啊,简简单单的查询被窝弄得乱七八糟的,如果你也遇到了页面上需要显示如图片所示的需求是,大可不必看上面黑色字体部分。下面的代码我想不必在经过我解释了:使用 :or语法
select
*
from
article article0_
where
article0_.article_id=(
select
max(article1_.article_id)
from
article article1_
where
article1_.status=1
and article1_.category=0
and article1_.sub_category=0
and article1_.article_id<?
)
orarticle0_.article_id=?
or article0_.article_id=(
select
min(article2_.article_id)
from
article article2_
where
article2_.status=1
and article2_.category=0
and article2_.sub_category=0
and article2_.article_id>?
)
order by
article0_.publish_date desc