例如，我有使用帶有此架構的 Mysql 資料庫的應用程式。
packages
id, name, order
1, 'Free', 3
2, 'Basic', 2
3, 'Extensive', 1
ads
id, date, package_id, package_expires_at
1, '2021-12-10 00:00:00', 1, '2022-01-10'
2, '2021-12-15 00:00:00', 3, '2021-12-28'
3, '2021-12-15 00:00:00', 3, '2022-01-10'
4, '2021-12-20 00:00:00', 2, '2022-01-10'
5, '2021-12-21 00:00:00', 1, '2021-12-28'
6, '2021-12-25 00:00:00', 1, '2021-12-28'
我需要實作的是選擇所有廣告，但先訂購沒有過期的包裹，by packages.order asc, package_expire_at desc然后訂購過期的date desc
例如假設我們在 '2021-12-29' 那么結果應該是
id, date, package_id, package_expires_at
3, '2021-12-15 00:00:00', 3, '2022-01-10'
4, '2021-12-20 00:00:00', 2, '2022-01-10'
1, '2021-12-10 00:00:00', 1, '2022-01-10'
6, '2021-12-25 00:00:00', 1, '2021-12-28'
5, '2021-12-21 00:00:00', 1, '2021-12-28'
2, '2021-12-15 00:00:00', 3, '2021-12-28'
我在小提琴上添加了架構
http://sqlfiddle.com/#!9/cedb45/9
uj5u.com熱心網友回復：
在 (My)SQL 中執行此操作的方式是提供一個虛擬欄位作為ORDER BY條件。像這樣的事情應該可以解決問題：
SELECT a.id
, a.date
, a.package_id
, a.package_expires_at
, IF(a.package_expires_at < CURRENT_DATE, 1, 0) AS is_expired
, IF(a.package_expires_at < CURRENT_DATE, p.`date`, p.`package_expires_at`) AS sort_date
, p.`order` AS sort_package_order
FROM ads AS a
INNER JOIN packages AS p
ON p.id = a.package_id
ORDER BY IF( a.package_expires_at < CURRENT_DATE, 1, 0 ) ASC
, IF( a.package_expires_at < CURRENT_DATE, 1, p.`order` ) ASC
, IF( a.package_expires_at < CURRENT_DATE, p.`date`, p.`package_expires_at` ) DESC
uj5u.com熱心網友回復：
使用兩個單獨的查詢，然后使用union它們（您的包裹訂單現在與其 ID 顛倒了）
select * from (SELECT * from ads WHERE package_expires_at >= '2021-12-29' order by package_id asc, package_expires_at desc ) as a
union
select * from (SELECT * from ads WHERE package_expires_at < '2021-12-29' order by date desc) as b
如果它們是動態的且沒有順序，請加入它們，然后執行上述操作。
select * from (SELECT ads.id,date,package_id,package_expires_at from ads left outer join packages on package_id = ads.id WHERE package_expires_at >= '2021-12-29' order by packages.order asc, package_expires_at desc ) as a
union
select * from ( SELECT ads.id,date,package_id,package_expires_at from ads left outer join packages on package_id = ads.id WHERE package_expires_at < '2021-12-29' order by date desc ) as b
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/396722.html
標籤：php mysql sql-order-by
上一篇：M1macOSMojave上的npm全域bin檔案夾權限錯誤
下一篇：比較同一表中同一行的主ID