通过在MySQL上开始和结束日期的复杂排序顺序

I have an events table with starting_date and ending_date. I want the to sort them like the following:

我有一个带有starting_date和ending_date的事件表。我希望按以下方式对它们进行排序:

There are 3 groups of rows and they should appear sequentially, each group with it's order. The groups are as follow:

有3组行,它们应按顺序出现,每组按顺序排列。小组如下:

• Group 1: starting_date = curdate() and ending_date = curdate(). It's rows should be ordered by id asc.

  第1组:starting_date = curdate()和ending_date = curdate()。它的行应按id asc排序。

• Group 2: starting_date <= curdate() and ending_date > curdate(). It's elements should be ordered by ending_date asc

  第2组:starting_date <= curdate()和ending_date> curdate()。它的元素应该由ending_date asc排序

• Group 3: starting_date > curdate(). It's elements should be ordered by starting date asc

  第3组:starting_date> curdate()。应该通过开始日期asc来排序元素

Supposing today is may 21, this is a sample of the output i need:

假设今天是5月21日,这是我需要的输出样本:

| id | starting_date | ending_date |

// Group 1
| 1  |    may 21     |    may 21   |
| 2  |    may 21     |    may 21   |

// Group 2
| *  |    may 20     |    may 23   |
| *  |    may 15     |    may 25   |
| *  |    may 17     |    may 26   |
| *  |    may 21     |    may 27   |

// Group 3
| *  |    may 23     |    may 25   |
| *  |    may 24     |    may 25   |

As you can see, each group appears in sequence, and each row inside a group is ordered by the specified order by.

如您所见,每个组按顺序出现,组内的每一行按指定的顺序排序。

So far I've tried using ORDER BY CASE WHEN sentences but I find myself unable to understand if I should:

到目前为止,我已尝试使用ORDER BY CASE WHEN句子,但我发现自己无法理解是否应该:

• Use multiple CASE ... END statements under the ORDER BY

在ORDER BY下使用多个CASE ... END语句

• Use multiple WHEN ... THEN statements inside an ORDER BY CASE ... END

在ORDER BY CASE ... END中使用多个WHEN ... THEN语句

• Both, multiple CASE with multiple when inside each.

两个,多个CASE,每个内部有多个。

• This can't be done using ORDER BY.

使用ORDER BY无法完成此操作。

This is one of the many SELECT sentences I've tried:

这是我尝试的众多SELECT句子之一:

select events.id, events.starting_date, events.ending_date from events
    where events.ending_date >= CURDATE()
    order by
        case 
            when (events.starting_date = curdate() and events.ending_date = (curdate() + interval 23 hour + interval 59 minute)) then 
            0
            when (events.starting_date <= curdate() and events.ending_date > curdate()) then
            1
            when (events.starting_date > curdate()) then
            2
        end asc

This query put rows on the correct group, but they aren't ordered inside their respective group.

此查询将行放在正确的组中,但它们不在其各自的组中排序。

This other one puts the first group at the end.

另一个将第一组放在最后。

order by

    case when (events.starting_date = curdate() and events.ending_date = (curdate() + interval 23 hour + interval 59 minute)) then events.id end asc,
    case when (events.starting_date <= curdate() and events.ending_date > curdate()) then events.ending_date end asc,
    case when (events.starting_date > curdate()) then events.starting_date end asc

What am I doing wrong?

我究竟做错了什么?

1 个解决方案

#1

The sorting logic you are attempting to codify requires two sorting variables. The first sort is based on to what group (1-3) each record belongs. The second sort happens within each group. The correct approach is to use the logic from the two attempts you made in your question. The general form of the ORDER BY is:

您尝试编码的排序逻辑需要两个排序变量。第一种是基于每条记录所属的组(1-3)。第二种排序发生在每个组中。正确的方法是使用您在问题中进行的两次尝试的逻辑。 ORDER BY的一般形式是:

ORDER BY group_number, something_else

where the group number is 1, 2, or 3, and the something else is the specific order for that group. But the catch here is that group_number and something_else each have a separate CASE statement. In the case of group 1, you would want ORDER BY 1, events.id. I also added an ELSE condition to the CASE expressions to handle default cases not covered, and any such records will appear in a group at the bottom of the result set, sorted by id.

组编号为1,2或3的位置,其他内容是该组的特定顺序。但是这里的问题是group_number和something_else都有一个单独的CASE语句。在组1的情况下,您需要ORDER BY 1,events.id。我还在CASE表达式中添加了一个ELSE条件来处理未覆盖的默认情况,任何此类记录都将出现在结果集底部的一个组中,按id排序。

SELECT events.id, events.starting_date, events.ending_date
FROM events
WHERE events.ending_date >= CURDATE()
ORDER BY
    (CASE
        WHEN (events.starting_date = CURDATE() AND
              events.ending_date = (CURDATE() + INTERVAL 23 HOUR + INTERVAL 59 MINUTE))
            THEN 1
        WHEN (events.starting_date <= CURDATE() AND
              events.ending_date > CURDATE())
            THEN 2
        WHEN (events.starting_date > CURDATE())
            THEN 3
        ELSE 4
    END),
    (CASE
        WHEN (events.starting_date = CURDATE() AND
              events.ending_date = (CURDATE() + INTERVAL 23 HOUR + INTERVAL 59 MINUTE))
            THEN events.id
        WHEN (events.starting_date <= CURDATE() AND
              events.ending_date > CURDATE())
            THEN events.ending_date
        WHEN (events.starting_date > CURDATE())
            THEN events.starting_date
        ELSE events.id
    END)