更新问题：
ERROR: column "Fruits" does not exist
运行Postgres 7.4(是的,我们正在升级)
为什么我不能按列别名进行排序？想要在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
截至目前的结果
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
期望的结果将是具有“其他”总计的一行,因此总共四行
(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
解决方法
您可以使用ORDER BY 1按第一个字段排序,即“Fruits”.这同样适用于GROUP BY
更新
对于订单,而不是按顺序执行,在…中创建一个新列说…第二个位置：
(CASE
WHEN "Fruits" = 'Apple' THEN 1
WHEN "Fruits" = 'Pear' THEN 2
WHEN "Fruits" = 'Grapes' THEN 3
ELSE 4 ) as Order
然后在你ORDER BY 2.
