ORA BY BY别名无效

更新问题： 
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
    
2020-02-19 7 条评论
分享

没有找到相关结果

    已邀请:

    驮帽俺篮号

            您可以使用
    ORDER BY 1
    按第一个字段“ Fruits”排序。 
    GROUP BY
    同样有效 更新资料 对于订单，不要在
    order by
    中执行
    case
    ，而是在第二个位置创建一个新列： 
    (CASE 
    WHEN \"Fruits\" = \'Apple\' THEN 1 
    WHEN \"Fruits\" = \'Pear\' THEN 2 
    WHEN \"Fruits\" = \'Grapes\' THEN 3 
    ELSE 4 ) as Order
    然后在你身上
    ORDER BY 2
    。     
    2020-02-19 0 0
    分享

    赣借

            可以在文档中找到其原因：   [ORDER BY列表中的每个表达式]可以是输出列（SELECT列表项）的名称或序号，也可以是由输入列值组成的任意表达式。 （我的重点） 原因是旧版本的SQL标准（SQL-92）仅允许按输出列名称或数字排序，而较新的版本允许按任意表达式排序，但这些表达式由输入列值组成。 其他答案已经包含适合您情况的各种解决方法。     
    2020-02-19 0 0
    分享

    钾涎净介

            别名是在订购依据之后分配的，因此您不能在订购依据中使用别名。使用此代替： 
    (CASE
    WHEN tof.\"TypeOfFruits\" = \'A\' THEN \'Apple\' 
    WHEN tof.\"TypeOfFruits\" = \'P\' THEN \'Pear\' 
    WHEN tof.\"TypeOfFruits\" = \'G\' THEN \'Grapes\' 
    ELSE \'Other\' END)
        
    2020-02-19 0 0
    分享

    薄扩络拜

            考虑这样的事情： 
    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
        
    2020-02-19 0 0
    分享

    澜悍景哭苟

            您可以尝试类似的方法……未经测试，但是我已经看到类似的查询。 让我知道它是否有效... 
    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
        
    2020-02-19 0 0
    分享

    购藏盗码韦

            尝试使用反引号（`）代替单引号或双引号来包装别名。 MySQL也有同样的问题；反引号解决了该问题。     
    2020-02-19 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.04321813583374