postgresql ORDER BY 别名不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6458669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY Alias not working
提问by Phill Pafford
UPDATING QUESTION:
更新问题：
ERROR: column "Fruits" does not exist
Running Postgres 7.4(Yeah we are upgrading)
运行 Postgres 7.4（是的，我们正在升级）
Why can't I ORDER BY the column alias? wants tof."TypeOfFruits" in the ORDER BY as well, why?
为什么我不能按列别名 ORDER？ORDER BY 中也需要“TypeOfFruits”，为什么？
SELECT (CASE
WHEN tof."TypeOfFruits" = 'A' THEN 'Apple'
WHEN tof."TypeOfFruits" = 'P' THEN 'Pear'
WHEN tof."TypeOfFruits" = 'G' THEN 'Grapes'
ELSE 'Other' END) AS "Fruits",
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('DAY', LOCALTIMESTAMP) AND DATE_TRUNC('DAY', LOCALTIMESTAMP) + INTERVAL '1 DAY'
THEN 1 ELSE 0 END) AS daily,
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('MONTH', LOCALTIMESTAMP) AND DATE_TRUNC('MONTH', LOCALTIMESTAMP) + INTERVAL '1 MONTH'
THEN 1 ELSE 0 END) AS monthly,
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('YEAR', LOCALTIMESTAMP) AND DATE_TRUNC('YEAR', LOCALTIMESTAMP) + INTERVAL '1 YEAR'
THEN 1 ELSE 0 END) AS yearly,
SUM(CASE WHEN r.order_date >= '01-01-2011 00:00:00' THEN 1 ELSE 0 END) AS lifetime
FROM reports AS r, "TypeOfFruits" AS tof
WHERE r.id = tof."ID"
GROUP BY "Fruits"
ORDER BY CASE
WHEN "Fruits" = 'Apple' THEN 1
WHEN "Fruits" = 'Pear' THEN 2
WHEN "Fruits" = 'Grapes' THEN 3
ELSE 4
END
Results as of now
截至目前的结果
Fruits;daily;monthly;yearly;lifetime
"Apple";17;1174;3136;3136
"Pear";28;94;94;94
"Grapes";0;191;490;490
"Other";0;2;27;27
"Other";0;0;1;1
"Other";0;0;27;27
"Other";0;6;28;28
"Other";0;58;229;229
"Other";0;3;3;3
"Other";0;0;1;1
Desired results would be one row with the "Other" total, so four rows altogether (x would be the total)
所需的结果将是“其他”总数的一行，因此总共四行（x 将是总数）
Fruits;daily;monthly;yearly;lifetime
"Apple";17;1174;3136;3136
"Pear";28;94;94;94
"Grapes";0;191;490;490
"Other";x;x;x;x
回答by Adriano Carneiro
You can use ORDER BY 1to order by the first field, which is "Fruits". The same is valid for GROUP BY
您可以使用ORDER BY 1第一个字段来订购，即“水果”。同样适用于GROUP BY
Update
更新
For the order, instead of doing the casein the order by, create a new column in.. say.. the second position:
对于订单，不要case在 中order by创建一个新列，比如在第二个位置：
(CASE
WHEN "Fruits" = 'Apple' THEN 1
WHEN "Fruits" = 'Pear' THEN 2
WHEN "Fruits" = 'Grapes' THEN 3
ELSE 4 ) as Order
Then in you ORDER BY 2.
然后在你ORDER BY 2。
回答by Peter Eisentraut
The reason for this can be found in the documentation:
原因可以在文档中找到：
Each expression [in the ORDER BY list] can be the name or ordinal number of an outputcolumn (SELECT list item), or it can be an arbitrary expression formed from input-column values.
每个表达式 [在 ORDER BY 列表中] 可以是输出列（SELECT 列表项）的名称或序数，也可以是由输入列值形成的任意表达式。
(my emphasis)
（我的重点）
The reason for this is that old versions of the SQL standard (SQL-92) only allowed sorting by output column name or number, whereas newer versions allow sorting by arbitrary expressions, but those expressions are formed from input column values.
这样做的原因是旧版本的 SQL 标准 (SQL-92) 只允许按输出列名称或编号排序，而新版本允许按任意表达式排序，但这些表达式由输入列值构成。
Other answers already contain various suitable workarounds for your case.
其他答案已经包含适用于您的案例的各种合适的解决方法。
回答by Joe Phillips
The alias is assigned after the order by so you can't use it in the order by. Use this instead:
别名是在 order by 之后分配的，因此您不能在 order by 中使用它。改用这个：
(CASE
WHEN tof."TypeOfFruits" = 'A' THEN 'Apple'
WHEN tof."TypeOfFruits" = 'P' THEN 'Pear'
WHEN tof."TypeOfFruits" = 'G' THEN 'Grapes'
ELSE 'Other' END)
回答by Alex Gitelman
Consider something like this:
考虑这样的事情：
SELECT * FROM (SELECT (CASE
WHEN tof."TypeOfFruits" = 'A' THEN 'Apple'
WHEN tof."TypeOfFruits" = 'P' THEN 'Pear'
WHEN tof."TypeOfFruits" = 'G' THEN 'Grapes'
ELSE 'Other' END) AS "Fruits",
(CASE
WHEN tof."TypeOfFruits" = 'A' THEN 1
WHEN tof."TypeOfFruits" = 'P' THEN 2
WHEN tof."TypeOfFruits" = 'G' THEN 3
ELSE 4 END) as NUM
FROM ..... <rest of your query without group by and order by .....
)
GROUP BY Fruits
ORDER BY NUM
回答by crowne
You could try something like this ... untested, but I've seen similar queries.
Let me know if it works...
你可以尝试这样的事情......未经测试，但我见过类似的查询。
让我知道它是否有效...
SELECT "Fruits",
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('DAY', LOCALTIMESTAMP) AND DATE_TRUNC('DAY', LOCALTIMESTAMP) + INTERVAL '1 DAY'
THEN 1 ELSE 0 END) AS daily,
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('MONTH', LOCALTIMESTAMP) AND DATE_TRUNC('MONTH', LOCALTIMESTAMP) + INTERVAL '1 MONTH'
THEN 1 ELSE 0 END) AS monthly,
SUM(CASE WHEN r.order_date
BETWEEN DATE_TRUNC('YEAR', LOCALTIMESTAMP) AND DATE_TRUNC('YEAR', LOCALTIMESTAMP) + INTERVAL '1 YEAR'
THEN 1 ELSE 0 END) AS yearly,
SUM(CASE WHEN r.order_date >= '01-01-2011 00:00:00' THEN 1 ELSE 0 END) AS lifetime
FROM reports AS r
,(SELECT "ID",
CASE
WHEN tof."TypeOfFruits" = 'A' THEN 'Apple'
WHEN tof."TypeOfFruits" = 'P' THEN 'Pear'
WHEN tof."TypeOfFruits" = 'G' THEN 'Grapes'
ELSE 'Other'
END AS "Fruits" FROM "TypeOfFruits" ) AS "tof"
WHERE r.id = tof."ID"
GROUP BY "Fruits"
ORDER BY CASE
WHEN "Fruits" = 'Apple' THEN 1
WHEN "Fruits" = 'Pear' THEN 2
WHEN "Fruits" = 'Grapes' THEN 3
ELSE 4
END
回答by xsqo
Try using backticks (`) instead of single/double quotes to wrap your alias name.
尝试使用反引号 (`) 而不是单/双引号来包装您的别名。
Had the same issue with MySQL; backticks fixed the problem.
MySQL 也有同样的问题；反引号解决了这个问题。
