根据不同列对表进行排序，具体取决于另一列发生的情况 [英] Sorting table by different cols, depending on what happens to another column

问题描述

这是我第一次问问题，英语不是我的母语.对于任何拼写错误或行为不当的行为，我深表歉意. 现在我的问题是: 我有一张像这样的桌子. (图1) 1.每次预订时间都是半小时 2.总是有booking_date，小时和分钟 3.有些行有交货日期，有些还没有 4.交货日期总是在预订日期之后

This is my first time I'm asking a question, and English is not my native language. I apologize for any misspelling or misbehaving beforehand. Now to my question: I have a table looking like this. (image 1) 1. Every booked time is half an hour long 2. There are always booking_date, hour and minute 3. Some rows have got delivery_date, some hasn’t yet 4. Delivery_date are always AFTER booking_date

无序表

例如，如果要打印的日期是(2018-12-01)，我希望按查看日期(2018-12-01)排序表，可以是booking_date或delivery_date，先到. AND应按每个(小时，分钟)的顺序排序.如下所示:(图片2)

If the day being printed out is for example (2018-12-01), I want the table to be ordered by the date being viewed (2018-12-01) either it is the booking_date OR delivery_date, which comes first. AND should be ordered by (hour, minute) of each. Like below: (image 2)

理想的订单

您会看到它从行01跳到08，然后又跳回到05，再跳到02.这是因为必须按小时和分钟进行排序.然而，delivery_date具有如此高的优先级，以至于它可以在行之间跳转(例如第05行)

AS you can see it jumps from row 01 to 08 and then back to 05, then to 02. It’s because it has to be ordered by hour and minute. And yet, the delivery_date has such priority that it jumps in between the rows (like row 05)

我尝试过以下SQL:

SELECT * FROM booking WHERE booking_date=$b_date OR DATE(delivery_date)=$b_date ORDER BY hour, minute ASC, HOUR(delivery_date), MINUTE(delivery_date) ASC

这将为我提供按小时，分钟正确排序的booking_date，但未正确排序delivery_date

This will give me the booking_date ordered correctly by hour, minute, but the delivery_date is not correctly ordered

然后我也进行了搜索，在Stackoverflow.com上找到并尝试了以下方法:

Then I have also searched, found on Stackoverflow.com and tried this one:

SELECT * FROM booking WHERE booking_date=$b_date OR DATE(delivery_date)=$b_date ORDER BY CASE WHEN booking_date=$b_date THEN hour, minute WHEN DATE(delivery_date)=$b_date  THEN HOUR(delivery_date), MINUTE(delivery_date) END ASC

这给了我以下错误:

检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'分钟WHEN DATE(delivery_date)= $ b_date THEN HOUR(delivery_date)，MINUTE(delivery_date)END'附近使用

check the manual that corresponds to your MySQL server version for the right syntax to use near ' minute WHEN DATE(delivery_date)=$b_date THEN HOUR(delivery_date), MINUTE(delivery_date) END' at line 1

，即小时"后的逗号.我接受它，它不喜欢逗号，所以我不能在ORDER BY中使用2列.当我只使用一列时，它会起作用，但是分钟数是错误的. 有没有办法在ORDER BY中使用2列?

and that is the comma after "hour". I take it, it doesn’t like the comma, so I can’t use 2 columns for ORDER BY. When I use only one column it works, but the minutes will be wrong. Is there a way to use 2 columns in ORDER BY?

推荐答案

THEN子句只能指定一个值.相反，您可以按分钟订购:

The THEN clause may only specify one value. Instead you can order by minutes:

SELECT *
FROM booking
WHERE booking_date=$b_date OR DATE(delivery_date)=$b_date
ORDER BY CASE WHEN booking_date=$b_date
                  THEN hour * 60 + minute
              WHEN DATE(delivery_date)=$b_date
                  THEN HOUR(delivery_date) * 60 + MINUTE(delivery_date)
         END ASC

这篇关于根据不同列对表进行排序，具体取决于另一列发生的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！