(select id from tmp where id < 5 order by id desc limit 2) union all (select id from tmp where id > 5 order by id limit 3) order by id;
mysql> select id from tmp; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +----+ 9 rows in set (0.00 sec)mysql> (select id from tmp where id < 5 order by id desc limit 2) -> union all -> (select id from tmp where id > 5 order by id limit 3) -> order by id; +----+ | id | +----+ | 3 | | 4 | | 6 | | 7 | | 8 | +----+ 5 rows in set (0.00 sec)
表的union操作最好不要做吧 select * from XXX order by id limit 3,8;
select * from XXX where id!=5 order by id limit 3,8;
union all
(select id from tmp where id > 5 order by id limit 3)
order by id;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (0.00 sec)mysql> (select id from tmp where id < 5 order by id desc limit 2)
-> union all
-> (select id from tmp where id > 5 order by id limit 3)
-> order by id;
+----+
| id |
+----+
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
+----+
5 rows in set (0.00 sec)
select * from XXX order by id limit 3,8;
如果能保证id是连续的,觉得linfirst的方法效率更好。不然只能用loveflea的方法才能保
证数据的正确性!如果不是:
办不到