mysql - 对查询中的 SQL 结果进行排序

Question

我有一个这样的查询：

SELECT A.*, 
       B.surname, 
       B.name, 
       C.url_address, 
       (SELECT Concat(file_path, '/', file_name) AS image 
        FROM   psuploadedfiles B 
        WHERE  enum = 3 
               AND category = A.id_user 
        ORDER  BY number ASC 
        LIMIT  1) AS image2, 
       (SELECT Concat(file_path, '/', file_name) AS image 
        FROM   psuploadedfiles B 
        WHERE  enum = 3 
               AND category = A.post_id 
        ORDER  BY number ASC 
        LIMIT  1) AS image 
FROM   psposts A 
       LEFT JOIN psuserdetails B 
              ON B.id_user = A.id_user 
       LEFT JOIN psuser C 
              ON C.id_user = A.id_user 
WHERE  A.enable = '1' 
       AND ( A.id_user = 21 
              OR ( A.id_user = '7' 
                    OR A.id_user = '1' 
                    OR A.id_user = '5' ) ) 
ORDER  BY date DESC 
LIMIT  0, 25; 

在psPosts表中，我有一个subscriptions_date列（包含 yyyy-mm-dd 格式的日期），其中subscriptions_date是发布帖子之前的日期。

如何对结果进行排序，以使顶部的帖子包含subscriptions_date当前或未来日期（即提升），而在其他帖子下方？

标签： mysqlsql

Answer 1

就像是...

ORDER BY 
    CASE WHEN A.subscriptions_date >= NOW() THEN 0 ELSE 1 END,
    date

第一个排序标准将数据分成两组，当前或未来订阅排在首位。在每个组中，数据然后按 排序date。