How can I factor out repeated expressions in an SQL Query? Column aliases don't seem to be the ticket
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 wit开发者_开发问答h it?
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'
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.)
精彩评论