php – Mysql在case语句中使用DESC

发布时间：2020-12-13 13:19:44 所属栏目：PHP教程来源：网络整理

导读：在下面的sql命令中我想在WHEN 1那么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.

在下面的sql命令中我想在WHEN 1那么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']};";

可能会返回SELECT中的排序字段,然后按该命名字段排序： –

SELECT 
     SQL_CALC_FOUND_ROWS i.*,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']};";

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!