我有一个mysql查询,它可以获取行到设计列表。在此列表中,我们有"普通"行,它们按其 id 排序,以及"特殊"行,它们按"special_end"(这是日期,现在在所有行中都是 NULL)参数排序。因此,结果中的"特殊"行应高于"正常"。
立即查询:
SELECT d.*, DATE_FORMAT(_created, '%d.%m.%Y') as _created_human, count(dp.id) photos_cnt, u.id user_id, u.class_name user_class_name, u.full_name user_full_name
FROM designs d
LEFT JOIN design_photo dp ON d.id=dp.design_id
LEFT JOIN cms_user u ON d.cms_user_id=u.id
WHERE 1=1 AND d.status_id=1 AND d.published=1 AND u.cms_user_status_id=1
GROUP BY d.id
ORDER BY d.special_end DESC, id DESC
LIMIT 20 OFFSET 0
我需要在此查询中升级 ORDER BY 块,以使用带有条件special_end参数对行进行排序:
- 如果 d.special_end>= DATE( NOW() ) -> 按d.special_end DESC 排序,
- 如果 d.special_end <= DATE( NOW() ) -> 按 id DESC 排序。
示例(id - 行 - special_end):
515 design4 2012-07-21
514 design3 2012-06-30
526 design5 null
513 design2 2012-05-30 (this date is <= DATE( NOW() ))
512 design1 null
511 design0 null
.
.
.
我已经尝试写类似的东西
ORDER BY CASE d.special_end WHEN d.special_end >= DATE( NOW() ) THEN d.special_end WHEN d.special_end <= DATE( NOW() ) THEN d.id END DESC
或
ORDER BY IF(d.special_end >= DATE( NOW() ), d.special_end, d.id) DESC
但没有结果。
感谢您的帮助。
按条件排序可能非常慢,我宁愿使用UNION
来实现相同的结果:
SELECT t.* FROM (
(
SELECT d.*, DATE_FORMAT(_created, '%d.%m.%Y') as _created_human, count(dp.id) photos_cnt, u.id user_id, u.class_name user_class_name, u.full_name user_full_name
FROM designs d
LEFT JOIN design_photo dp ON d.id=dp.design_id
LEFT JOIN cms_user u ON d.cms_user_id=u.id
WHERE d.special_end >= DATE(NOW()) AND d.status_id=1 AND d.published=1 AND u.cms_user_status_id=1
GROUP BY d.id
ORDER BY d.special_end DESC
) UNION (
SELECT d.*, DATE_FORMAT(_created, '%d.%m.%Y') as _created_human, count(dp.id) photos_cnt, u.id user_id, u.class_name user_class_name, u.full_name user_full_name
FROM designs d
LEFT JOIN design_photo dp ON d.id=dp.design_id
LEFT JOIN cms_user u ON d.cms_user_id=u.id
WHERE (d.special_end < DATE(NOW()) OR d.special_end IS NULL) AND d.status_id=1 AND d.published=1 AND u.cms_user_status_id=1
GROUP BY d.id
ORDER BY d.id DESC
)
) t LIMIT 20 OFFSET 0
当有多个分组依据或排序要求时,始终使用 UNION 拆分查询。只需将您的查询与 UNION 运算符拆分并进行排序即可。
Select * from Something where <condtion> order by 1 desc
UNION
Select * from Something where <condtion> order by 2 desc