order by语句语法错误

ghhaqwfi 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(398)

我刚刚开始学习sql，在尝试从第一个select块排序数据时遇到了一个order by error（错误代码1064:您的sql语法有错误）。问题解决了，当我把这个顺序语句移到第二或第三个街区时，我只是好奇为什么会这样？

SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers
WHERE points < 2000
ORDER BY first_name

UNION 

SELECT
    customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000

UNION

SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points > 3000

sql mysql Database

来源：https://stackoverflow.com/questions/62171276/order-by-statement-syntax-error-in-sql-database

2条答案

按热度按时间

shyt4zoc1#

使用 case :

SELECT customer_id, first_name, points,
       (CASE WHEN points < 2000 then 'Bronze'
             WHEN points < 3000 then 'Silver'
             ELSE 'Gold'
        END) as type
FROM customers;

赞(0）回复(0）举报 2021-07-26

enxuqcxy2#

当使用 UNION 带着一个 ORDER BY 条款 ORDER BY 在集合的末尾 UNION 如下所示。

SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers
WHERE points < 2000

UNION 

SELECT
    customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000

UNION

SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points > 3000

ORDER BY first_name

sql server查看 UNION 作为一个结果集，您只需排序一个结果集，即 UNION .
另外，请参见本文中来自microsoft的示例：https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15

赞(0）回复(0）举报 2021-07-26

我来回答

order by语句语法错误

2条答案

相关问题

热门标签

最新问答