带有HOUR的mySQL内部联接(CONVERT_TZ

Question

我正在尝试执行下面的SQL，但我得到了错误：错误代码：1054。子句的"中的未知列"t.tid">

有人能帮我把它修好吗？

SELECT SUM(amount) AS A, HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00')) AS tid FROM transactions AS t 
left join hours AS h on t.tid=h.hours
GROUP BY HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00'))
order by HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00'));

Answer 1

错误消息是响亮而清晰的Error Code: 1054. Unknown column 't.tid' in 'on clause'.你不能在WHERE子句中直接使用你的ALIAS：

尝试

SELECT SUM(amount) AS A, HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00')) AS tid FROM transactions AS t 
left join hours AS h on HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00'))=h.hours
GROUP BY HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00'))
order by HOUR(CONVERT_TZ(timeframe, 'UTC', '+3:00'))