MySQL，排序依据 + 每隔一行

Question

不确定以前是否曾被问过，但不确定要寻找什么。那么问题来了:

MySQL 表包含字段

A, B, C, D

A is 1 or 0
B is 1 or 0
C is 1, 2, 3 or 4
D is a date

到目前为止，B 被忽略，“order by”是:

A DESC, C DESC, D ASC

现在想要的是拥有

A DESC, B "1 every other row else 0", C DESC, D ASC

所以我们会得到像这样排序的行

[A, 1, C, D]
[A, 0, C, D]
[A, 1, C, D]
[A, 0, C, D]
[A, 1, C, D]
[A, 0, C, D]
[A, 1, C, D]
[A, 0, C, D] // last 0
[A, 1, C, D]
[A, 1, C, D]
[A, 1, C, D]

知道如何在 SQL 中执行此操作吗？这是关于按此顺序选择行，因此 SELECT ... ORDER BY ...

谢谢!

Answer 1

所需的排序依据:

order by 
    A DESC, 
    IF(B = 1, 2*(@odd := @odd + 1), 2*(@even := @even + 1) + 1),
    C DESC, 
    D ASC;

以下是完整的演示。

SQL:

-- data
create table t1(A INT, B INT, C INT, D date);
insert into t1 values
(1,1,1,'2016-01-01'),
(1,1,2,'2016-01-02'),
(1,1,3,'2016-01-03'),
(1,1,4,'2016-01-04'),
(1,1,1,'2016-01-04'),
(1,1,2,'2016-01-05'),
(1,1,3,'2016-01-06'),
(1,0,4,'2016-01-07'),
(1,0,1,'2016-01-08'),
(1,0,2,'2016-01-09');
select * from t1 order by A DESC, C DESC, D ASC;

-- query wanted
select t1.* 
from t1 CROSS JOIN (select @even := 0, @odd := 0) param
order by 
    A DESC, 
    IF(B = 1, 2*(@odd := @odd + 1), 2*(@even := @even + 1) + 1),
    C DESC, 
    D ASC;

输出:

mysql> select * from t1 order by A DESC, C DESC, D ASC;
+------+------+------+------------+
| A    | B    | C    | D          |
+------+------+------+------------+
|    1 |    1 |    4 | 2016-01-04 |
|    1 |    0 |    4 | 2016-01-07 |
|    1 |    1 |    3 | 2016-01-03 |
|    1 |    1 |    3 | 2016-01-06 |
|    1 |    1 |    2 | 2016-01-02 |
|    1 |    1 |    2 | 2016-01-05 |
|    1 |    0 |    2 | 2016-01-09 |
|    1 |    1 |    1 | 2016-01-01 |
|    1 |    1 |    1 | 2016-01-04 |
|    1 |    0 |    1 | 2016-01-08 |
+------+------+------+------------+
10 rows in set (0.00 sec)

-- query wanted
mysql> select t1.*
    -> from t1 CROSS JOIN (select @even := 0, @odd := 0) param
    -> order by
    -> A DESC,
    -> IF(B = 1, 2*(@odd := @odd + 1), 2*(@even := @even + 1) + 1),
    -> C DESC,
    -> D ASC;
+------+------+------+------------+
| A    | B    | C    | D          |
+------+------+------+------------+
|    1 |    1 |    1 | 2016-01-01 |
|    1 |    0 |    4 | 2016-01-07 |
|    1 |    1 |    2 | 2016-01-02 |
|    1 |    0 |    1 | 2016-01-08 |
|    1 |    1 |    3 | 2016-01-03 |
|    1 |    0 |    2 | 2016-01-09 |
|    1 |    1 |    4 | 2016-01-04 |
|    1 |    1 |    1 | 2016-01-04 |
|    1 |    1 |    2 | 2016-01-05 |
|    1 |    1 |    3 | 2016-01-06 |
+------+------+------+------------+
10 rows in set (0.00 sec)