UNION 两个有序的 MySQL 语句 [英] UNION two ordered MySQL statements

问题描述

我有一个包含 3 列(id、名称、代码)和 10 行的表格.某些行没有代码，因此某些列是空的.我想要完成的是选择 code 列不为空的行，首先按最后插入的顺序排序，然后是 code 列为空的所有行按最后插入的顺序.我试过了

I have a table with 3 columns (id, name, code) and 10 rows. Some of the rows don't have a code so that column is empty for some. What I'm trying to accomplish is SELECT the rows with code column not empty first ordered by last inserted followed by all rows with code column empty ordered by last inserted. I have tried

(SELECT * from tablename WHERE code <> '' ORDER BY ID DESC) UNION
(SELECT * from tablename WHERE code = '' ORDER BY ID DESC)

UNION 有效，但订单无效.我在这里阅读了其他问题，发现添加 ORDER BY 就像我添加的那样不起作用，我应该在最后添加它，但这不会帮助我完成我想要的，并且会混合具有 code 带有没有的行.

The UNION works but the order does not. I have read here about other questions and found out adding ORDER BY like I added will not work and I should add it at the end but that would not help me accomplish what I want and will mix rows that have a code with rows that don't.

有没有办法让我正在寻找的东西取得成功?

Is there a way to succeed with what I'm looking for?

推荐答案

我认为你只需要将排序逻辑放在 ORDER BY 子句中

I think you just need to put your sort logic in the ORDER BY clause

SELECT id, name, code
FROM tablename
ORDER BY code = '', ID desc;

这篇关于UNION 两个有序的 MySQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！