如何提取 SQL 查询中的重复表达式？列别名似乎不是问题

发布于 2024-09-03 10:58:23 字数 1221 浏览 5 评论 0原文

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

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 手册：

标准 SQL 不允许引用 WHERE 子句中的列别名。这施加限制是因为当 WHERE 子句被求值时，列值可能尚未确定确定。

因此，这种方法似乎已经行不通了。

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

原文

So, I've got a query that looks something like this:

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'

If you're like me, you probably start thinking that maybe it would improve readability and possibly the performance of this query if I wasn't asking it to compute CONVERT_TZ(callTime,'+0:00','-7:00') four separate times.

So I try to create a column alias for that expression and replace further occurances with that alias:

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'

This is when I learned, to quote the MySQL manual:

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.

So, that approach would seem to be dead in the water.

How is someone writing queries with recurring expressions like this supposed to deal with it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

离线来电— 2024-09-10 10:58:23

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

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'

You can define aliases in a derived table and then reference them in the outer query:

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'

回复收藏 0 原文

水波映月 2024-09-10 10:58:23

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' having
    callTimeZoned >= '2010-04-25' AND
    callTimeZoned <= '2010-05-25'

（请注意倒数第三行从 AND 到 having 的切换。）

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' having
    callTimeZoned >= '2010-04-25' AND
    callTimeZoned <= '2010-05-25'

(Note the switch from AND to having on the third to last line.)

回复收藏 0 原文

~没有更多了~