为什么我不能在下一个 SELECT 表达式中使用列别名?

问题描述

我可以修改下一个以在表达式 ROUND(avg_time * cnt, 2) 中使用列别名 avg_time 和 cnt 吗?

Can I modify the next to use the column aliases avg_time and cnt in an expression ROUND(avg_time * cnt, 2)?

SELECT 
    COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
    MAX(time) as max_time, 
    ROUND(AVG(time), 2) as avg_time, 
    MIN(time) as min_time, 
    COUNT(path) as cnt, 
    ROUND(avg_time * cnt, 2) as slowdown, path
FROM 
    loadtime
GROUP BY
    path
ORDER BY
    avg_time DESC
LIMIT 10;

它引发下一个错误:

ERROR:  column "avg_time" does not exist
LINE 7:  ROUND(avg_time * cnt, 2) as slowdown, path

然而，下一个工作正常(使用主要表达式而不是列别名:

The next, however, works fine (use primary expressions instead of column aliases:

SELECT 
    COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
    MAX(time) as max_time, 
    ROUND(AVG(time), 2) as avg_time, 
    MIN(time) as min_time, 
    COUNT(path) as cnt, 
    ROUND(AVG(time) * COUNT(path), 2) as slowdown, path
FROM 
    loadtime
GROUP BY
    path
ORDER BY
    avg_time DESC
LIMIT 10;

推荐答案

您可以在 GROUP BY 或 HAVING 语句中使用以前创建的别名，但不能在 SELECT 或 WHERE 语句.这是因为程序同时处理所有的 SELECT 语句并且还不知道别名的值.

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

解决方案是将查询封装在子查询中，然后别名在外面可用.

SELECT stddev_time, max_time, avg_time, min_time, cnt, 
       ROUND(avg_time * cnt, 2) as slowdown
FROM (
        SELECT 
            COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
            MAX(time) as max_time, 
            ROUND(AVG(time), 2) as avg_time, 
            MIN(time) as min_time, 
            COUNT(path) as cnt, 
            path
        FROM 
            loadtime
        GROUP BY
            path
        ORDER BY
            avg_time DESC
        LIMIT 10
   ) X;

这篇关于为什么我不能在下一个 SELECT 表达式中使用列别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！