为什么不能在下一个SELECT表达式中使用列别名? [英] Why can't I use column aliases in the next SELECT expression?

问题描述

我可以修改下一个在表达式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表达式中使用列别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！