ORDER BY别名不起作用 [英] ORDER BY Alias not working

问题描述

 错误：列Fruits不存在
  

运行Postgres 7.4（是的，我们正在升级）

为什么我不能ORDER BY列别名？在ORDER BY中也需要tofTypeOfFruits，为什么？

  SELECT（CASE 
 when tof。） TypeOfFruits='A'THEN'Apple'
 when tof。TypeOfFruits='P'THEN'Pear'
 when tof。TypeOfFruits='G'THEN'Grapes'
 （'DAY'，LOCALTIMESTAMP）和DATE_TRUNC（'DAY'，LOCALTIMESTAMP）+ INTERVAL'1 DAY' 
 THEN 1 ELSE 0 END）as daily，
 SUM（CASE WHERE 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年'
然后1 ELSE 0结束）每年，
 SUM（案例r.order_date> = '01 -01- 2011 00:00:00'THEN 1 ELSE 0 END）AS lifetime 
 FROM reports AS r，TypeOfFruitsAS tof 
 WHERE r.id = tof。ID
 GROUP BY水果
 ORDER BY CASE 
当Fruits='Apple'THEN 1 
当Fruits='梨'THEN 2 
当Fruits='葡萄'THEN 3 
 ELSE 4 
 END 
  

截至目前为止

 水果;每日;每月;每年;终生
Apple; 17; 1174; 3136; 3136 
梨 ; 28; 94; 94; 94 
葡萄; 0; 191; 490; 490 
其他; 0; 2; 27; 27 
其他; 0; 0; 1; 1 
其他; 0; 0; 27; 27 
其他; 0; 6; 28; 28 
其他; 0; 58; 229; 229 
其他; 0; 3; 3; 3 
其他; 0; 0; 1; 1 
  

期望的结果将与其他总计一行，所以四行共
（x将是总数）

 水果;每日;每月;每年;终生
苹果; 17; 1174; 3136; 3136 
梨; 28; 94; 94; 94 
葡萄; 0; 191; 490; 490 
其他; x; x; x; x 
  




解决方案

您可以使用 ORDER BY 1 按第一个字段排序，即Fruits。对于 GROUP BY

更新

对于订单，而不是通过在顺序中执行个案，创建一个新栏目..说..第二个位置：



 （CASE 
当Fruits='Apple' THEN 1 
当Fruits='Pear'THEN 2 
当Fruits='Grapes'THEN 3 
 ELSE 4）作为订单
  

然后在您 ORDER BY 2 。

UPDATING QUESTION:

ERROR:  column "Fruits" does not exist

Running Postgres 7.4(Yeah we are upgrading)

Why can't I ORDER BY the column alias? wants tof."TypeOfFruits" in the ORDER BY as well, why?

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)

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

解决方案

You can use ORDER BY 1 to order by the first field, which is "Fruits". The same is valid for GROUP BY

Update

For the order, instead of doing the case in the order by, create a new column in.. say.. the second position:

(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别名不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！