sql - 如何在 SQL 查询中提取重复的表达式？列别名似乎不是票

Question

所以，我有一个看起来像这样的查询:

SELECT id, 
    DATE_FORMAT(CONVERT_TZ(callTime,'+0:00','-7:00'),'%b %d %Y') as callDate, 
    DATE_FORMAT(CONVERT_TZ(callTime,'+0:00','-7:00'),'%H:%i') as callTimeOfDay, 
    SEC_TO_TIME(callLength) as callLength
FROM cs_calldata WHERE 
    customerCode='999999-abc-blahblahblah' AND 
    CONVERT_TZ(callTime,'+0:00','-7:00') >= '2010-04-25' AND
    CONVERT_TZ(callTime,'+0:00','-7:00') <= '2010-05-25'

如果你像我一样，你可能会开始想，如果我不要求它计算 CONVERT_TZ(callTime,'+0:00','-7:00')，它可能会提高这个查询的可读性和性能。四个不同的时间。

因此，我尝试为该表达式创建一个列别名，并用该别名替换进一步出现的情况:

SELECT id, 
    CONVERT_TZ(callTime,'+0:00','-7:00') as callTimeZoned,
    DATE_FORMAT(callTimeZoned,'%b %d %Y') as callDate, 
    DATE_FORMAT(callTimeZoned,'%H:%i') as callTimeOfDay, 
    SEC_TO_TIME(callLength) as callLength
FROM cs_calldata WHERE 
    customerCode='5999999-abc-blahblahblah' AND 
    callTimeZoned >= '2010-04-25' AND
    callTimeZoned <= '2010-05-25'

这是我学到的，引用 MySQL 手册:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

因此，这种方法似乎已无济于事。

用这样的重复表达式编写查询的人应该如何处理它？

Answer 1

您可以在派生表中定义别名，然后在外部查询中引用它们:

SELECT callTimeZoned, callLength,
    DATE_FORMAT(callTimeZoned,'%b %d %Y') as callDate, 
    DATE_FORMAT(callTimeZoned,'%H:%i') as callTimeOfDay 
FROM (
    SELECT
        CONVERT_TZ(callTime,'+0:00','-7:00') as callTimeZoned,
        SEC_TO_TIME(callLength) as callLength
    FROM cs_calldata
    WHERE customerCode='5999999-abc-blahblahblah'
) AS d
WHERE 
    callTimeZoned BETWEEN '2010-04-25' AND '2010-05-25'