如何在MySQL中以相​​反的顺序合并2个结果集 [英] How to merge 2 result sets with opposite order in MySQL

问题描述

MySQL表结构:

id    date
1     2014-12-01
2     2014-12-02
3     2014-12-03
4     2014-12-04
5     2014-12-05
6     2014-12-06
7     2014-12-07

首先选择:

select * from table where id in (1,2,3,4) order by data desc;

第二次选择:

select * from table where id in (5,6,7) order by data asc;

我尝试在之间使用UNION，但失败了.似乎ORDER BY只能使用一次. 合并后，我想使用LIMIT OFFSET进行分页.

I tried to use UNION between and failed. It seems the ORDER BY could be used only once. After the merging, I'd like to use LIMIT OFFSET for pagination.

预期结果将是:

id    date
4     2014-12-04
3     2014-12-03
2     2014-12-02
1     2014-12-01
5     2014-12-05
6     2014-12-06
7     2014-12-07

我在项目中使用Laravel.它的分页非常容易.所以我想知道如何使用Laravel Eloquent来实现它.

I'm using Laravel in my project. Its pagination is very easy. So I'd like to know how to implement it with Laravel Eloquent.

推荐答案

尝试一下. 提琴手演示

Try this. Fiddler Demo

SELECT * FROM 
(
      select * from table where id in (1,2,3,4) order by data desc
) Test
UNION
SELECT * FROM 
(
      select * from table where id in (5,6,7) order by data asc
) Test2

这篇关于如何在MySQL中以相​​反的顺序合并2个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！