MySQL查询按两个字段排序

I have a MySQL table with the following fields:

我有一个包含以下字段的MySQL表:

id, morning, evening, event, other-fields

For about one week, each day some event will occur either in morning or in evening. Thus the events are recorded in the table with the "day-number" stored in "morning" or "evening" field(if the event is to occur in morning, then morning field contains the "day-number" and evening field contains 0. and vise-versa). These entries are not made sequence-wise or day wise but as per the user's own will.

大约一周,每天都会有一些事件发生在早上或晚上。因此,事件被记录在表中,“day-number”存储在“morning”或“evening”字段中(如果事件发生在早晨,则morning字段包含“day-number”,而evening字段包含0反之亦然。这些条目不按顺序或按日制作,而是按照用户自己的意愿制作。

I need to fetch these records in proper order i.e. Sort the records day-wise in ascending order and records of each day need to be sorted session-wise(i.e. morning first and then evening ones).

我需要按正确的顺序获取这些记录,即按升序顺序对记录进行排序,并且每天的记录需要按会话顺序排序(即先上午然后是晚上)。

Please help with an idea to order the data on these two fields.

请帮忙想出订购这两个领域的数据。

Following is some sample data for three days:

以下是三天的一些样本数据:

id,     mor,       even,     otherstuff....
1         1          0
2         3          0
3         3          0
4         2          0
5         3          0
6         3          0
--------------------------
7         0          1
8         0          2
9         0          3
10        0          3
----------------------

Following is the same above data in the format I need to fetch it:

以下是我需要获取它的格式相同的上述数据:

id,      mor,       even,     otherstuff....
1         1          0
2         0          1
--------------------------
3         2          0
4         0          2
------------------------
5         3          0
6         3          0
7         3          0
8         3          0
9         0          3
10        0          3

1 个解决方案

#1

If one of them is always zero, the day number will be equal to mor + even so you can sort by that.

如果其中一个总是为零,则天数将等于mor +,即使您可以按此排序。

select *
from your_table
order by (mor + even), even;

Since even = 0 for "day" records, they will appear before any "even" records on the same day number.

由于“day”记录的均匀= 0,它们将出现在同一天的任何“偶数”记录之前。

#1