MYSQL order 根据where子句匹配

Question

数据库:

SELECT `item`.* 
FROM   `item` 
       LEFT JOIN `item_category` 
              ON `item_category`.`Item` = `item`.`id` 
       LEFT JOIN `item_sub_category` 
              ON `item_sub_category`.`Item` = `item`.`id` 
       LEFT JOIN `cart` 
              ON `cart`.`item` = `item`.`id` 
                 AND `cart`.`user` = 3 
       LEFT JOIN `user_users` 
              ON `user_users`.`id` = 3 
WHERE  `cart`.`item` IS NULL 
       AND `item_sub_category`.`sub_category` IN( '65', '66', '67', '68' ) 
        OR `item_category`.`category` IN( '35', '36' ) 
GROUP  BY `item`.`id` 
ORDER  BY `item`.`id` DESC, 
          `item`.`feature` DESC 
LIMIT  4 

如您所见，where 条件中有 OR。我想优先考虑两个条件都成立的行。我怎样才能达到这个结果？

Answer 1

添加一个

, (CASE WHEN CONDITION1 AND CONDITION2 THEN 0 ELSE 1 END)

到您的 ORDER BY 子句，其中 CONDITION1 和 CONDITION2 是您的两个条件。

编辑以在查询中显示并修复 WHERE 子句中的 AND/OR 问题

SELECT `item`.* 
FROM   `item` 
   LEFT JOIN `item_category` 
          ON `item_category`.`Item` = `item`.`id` 
   LEFT JOIN `item_sub_category` 
          ON `item_sub_category`.`Item` = `item`.`id` 
   LEFT JOIN `cart` 
          ON `cart`.`item` = `item`.`id` 
             AND `cart`.`user` = 3 
   LEFT JOIN `user_users` 
          ON `user_users`.`id` = 3 
WHERE  `cart`.`item` IS NULL 
   AND 
   ( 
     `item_sub_category`.`sub_category` IN( '65', '66', '67', '68' ) 
    OR 
      `item_category`.`category` IN( '35', '36' ) 
   )
ORDER  BY `item`.`id` DESC, 
      `item`.`feature` DESC,
      CASE WHEN `item_sub_category`.`sub_category` IN( '65', '66', '67', '68' )
               AND `item_category`.`category` IN( '35', '36' )
           THEN 0
           ELSE 1 END ASC
LIMIT  4 

它所做的是按虚拟列排序，当你想给它优先级时它是 0，当你不想给它优先级时它是 1。