order by语句语法错误

ghhaqwfi  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(405)

我刚刚开始学习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
shyt4zoc

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;
enxuqcxy

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

相关问题