在下面的sql命令中我想在WHEN 1那么i.id行之后使用DESC.我想如果sortable字段是1那么order by必须有这样的命令,
- ORDER BY i.id DESC
- 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']};";
请注意,您可能必须将字段强制转换为数据类型才能执行此操作.
