id name like 1 make ball 2 jeffery pingpang 3 lucy food 5 dave water 6 jack dddd 7 sunny test比如这六条数据 我知道了 name 为 dave 的人, 那么我想找出来jeffery,lucy,dave,jack,sunny这五条数据, 如果以lucy为条件,那么找出来make,jeffery,lucy,dave,jack这五条数据。也就是一条件为中心的五条数据。
(select * from table1 where id>=(select id from table1 where name='dave') order by id limit 3) union all (select * from table1 where id<(select id from table1 where name='dave') order by id limit 2)
limit 是从上往下截取数据条数 有从下往上截取条数的么
假设你的SQL语句存为VIEW1,且字段唯一 (select * from table1 where id>2009 order by id limit 2) union all (select * from table1 where id<=2009 order by id limit 3)
看了三楼的代码我写了 (SELECT `year` FROM wt_brandnotes WHERE `year`<2009 GROUP BY `year` ORDER BY `year` DESC LIMIT 2) UNION ALL (SELECT `year` FROM wt_brandnotes WHERE `year`>=2009 GROUP BY `year` ORDER BY `year` LIMIT 3) 结果 2008 2007 2009 2010 2012只是 2008 和 2007 顺序不对了
select id from tb where id<2008 order by id desc limit 2; union all select id from tb where id>2008 order by id limit 2;
select * from ( (SELECT `year` FROM wt_brandnotes WHERE `year`<2009 GROUP BY `year` ORDER BY `year` DESC LIMIT 2) UNION ALL (SELECT `year` FROM wt_brandnotes WHERE `year`>=2009 GROUP BY `year` ORDER BY `year` LIMIT 3) ) a order by `year`
贴建表及插入记录的SQL,及要求结果出来看看
2 jeffery pingpang
3 lucy food
5 dave water
6 jack dddd
7 sunny test比如这六条数据 我知道了 name 为 dave 的人, 那么我想找出来jeffery,lucy,dave,jack,sunny这五条数据, 如果以lucy为条件,那么找出来make,jeffery,lucy,dave,jack这五条数据。也就是一条件为中心的五条数据。
union all
(select * from table1 where id<(select id from table1 where name='dave') order by id limit 2)
(select * from table1 where id>2009 order by id limit 2)
union all
(select * from table1 where id<=2009 order by id limit 3)
UNION ALL
(SELECT `year` FROM wt_brandnotes WHERE `year`>=2009 GROUP BY `year` ORDER BY `year` LIMIT 3)
结果
2008
2007
2009
2010
2012只是 2008 和 2007 顺序不对了
union all
select id from tb where id>2008 order by id limit 2;
(SELECT `year` FROM wt_brandnotes WHERE `year`<2009 GROUP BY `year` ORDER BY `year` DESC LIMIT 2)
UNION ALL
(SELECT `year` FROM wt_brandnotes WHERE `year`>=2009 GROUP BY `year` ORDER BY `year` LIMIT 3)
) a order by `year`