php - Mysql在case语句中使用DESC

Question

在下面的 sql 命令中，我想在 WHEN 1 THEN i.id 行之后使用 DESC。我想如果 sortable 字段是 1 那么 order by 必须像这个命令一样，

ORDER BY i.id DESC

Mysql :

  SELECT 
     SQL_CALC_FOUND_ROWS i.* , 
     c.title AS category_name, 
     u.name, 
     u.family, 
     i.thumb_image,
     CONCAT( u.name, ' ', u.family ) AS author,
     tumbnail_image_width,
     tumbnail_image_height
  FROM   contents i
  JOIN   categories c ON c.id = i.category
  JOIN   users u ON u.id = i.posted_by
  JOIN   settings s ON s.portal = i.portal
  WHERE 
         i.portal = '{$portal_id}'
         AND CASE WHEN post_type = 4
              THEN date(NOW()) BETWEEN i.from_dateTime AND i.to_dateTime 
         ELSE post_type = 1
         END
  AND i.t_status = 1
  ORDER BY
     CASE (SELECT sortable FROM settings)
        WHEN 1 THEN i.id 
        WHEN 2 THEN i.date_time
        WHEN 3 THEN i.order_display
     END                         
  LIMIT {$portalSettings['display_post_count']};";   

Answer 1

可能在 SELECT 中带回排序字段，然后按该命名字段排序:-

  SELECT 
     SQL_CALC_FOUND_ROWS i.* , 
     c.title AS category_name, 
     u.name, 
     u.family, 
     i.thumb_image,
     CONCAT( u.name, ' ', u.family ) AS author,
     tumbnail_image_width,
     tumbnail_image_height,
     CASE (s.sortable)
        WHEN 1 THEN 100000000 - i.id 
        WHEN 2 THEN i.date_time
        WHEN 3 THEN i.order_display
     END  AS SortField
  FROM   contents i
  JOIN   categories c ON c.id = i.category
  JOIN   users u ON u.id = i.posted_by
  JOIN   settings s ON s.portal = i.portal
  WHERE 
         i.portal = '{$portal_id}'
         AND CASE WHEN post_type = 4
              THEN date(NOW()) BETWEEN i.from_dateTime AND i.to_dateTime 
         ELSE post_type = 1
         END
  AND i.t_status = 1
  ORDER BY SortField                  
  LIMIT {$portalSettings['display_post_count']};";

请注意，您可能必须将字段转换为数据类型才能执行此操作。