Mysql union all 之后仍然会重新排序问题 

SELECT
*
FROM
(
(
SELECT
t1.*, t2.title,
t2.content,
t2.create_time,
t2.create_uid,
t2.`status`,
t2.sort_field,
t2.update_time
FROM
qd_notice_user t1
LEFT JOIN qd_notice t2 ON t2.id = t1.notice_id
WHERE
user_id =1
AND sort_field IS NOT NULL
ORDER BY
sort_field DESC
)
UNION ALL
(
SELECT
t1.*, t2.title,
t2.content,
t2.create_time,
t2.create_uid,
t2.`status`,
t2.sort_field,
t2.update_time
FROM
qd_notice_user t1
LEFT JOIN qd_notice t2 ON t2.id = t1.notice_id
WHERE
user_id =1
AND sort_field IS NULL
ORDER BY
create_time DESC
)
) u_table

如图，目的是实现分段排序，使用union all 之后仍然会自动排序，需要加上Limit，解决办法是 在DESC后加上 limit 9999

posted @ 2022-08-04 17:26  unlimitedbladeworks  阅读(939)  评论(0)    收藏  举报