MySQL names resolution order in query

I开发者_如何学运维 switched in my app from SQLite to MySQL

I have nested queries (the number is variable so it's useful to me keeping always same names) like this:

SELECT DATE_FORMAT(datetime, '%Y-%m-%d %H:00:00') as datetime FROM (
    SELECT datetime FROM (...) AS T
) AS T GROUP BY datetime

in SQLIte GROUP BY datetime is evaulated on formatted date so the grouping takes place while in MySQL plain passed datetime is used so I have no grouping.

To better explain this works in MySQL but brakes my cycle having to rename columns and/or tables AS at each loop:

SELECT DATE_FORMAT(datetime, '%Y-%m-%d %H:00:00') as new_datetime FROM (
    SELECT datetime FROM (...) AS T
) AS T GROUP BY new_datetime

So, is there a way to make MySQL group the datetime I want?

If you want to group by the formatted datetime, pass that into the group by clause:

GROUP BY DATE_FORMAT(datetime, '%Y-%m-%d %H:00:00')

This will avoid multiple rows on different minutes/seconds.

To avoid calling DATE_FORMAT twice, you can use something like:

SELECT @dtm := DATE_FORMAT(datetime, '%Y-%m-%d %H:00:00') as datetime FROM tablename AS T GROUP BY @dtm