对于sql server来说:
GROUP BY和ORDER BY同时存在的情况是,ORDER BY对GROUP BY后的结果再进行排序的,所以ORDER BY后面的排序字段需要在SELECT里出现的,即ORDER
BY
子句中的列必须包含在聚合函数或
GROUP
BY
子句中。
如:
下面这个就是错的
SQL code?
1 | SELECT [col1] ,[col2] FROM [tb] GROUP BY [col1] ,[col2] ORDER BY [col1] ,[col2] ,[col3] |
而下面的都是对的
SQL code?
1 2 3 | SELECT [col1] ,[col2], MAX ([col3]) FROM [tb] GROUP BY [col1] ,[col2] ORDER BY [col1] ,[col2] , MAX ([col3]) SELECT [col1] ,[col2], MAX ([col3]) AS [col3] FROM [tb] GROUP BY [col1] ,[col2] ORDER BY [col1] ,[col2] ,[col3] SELECT [col1] ,[col2] FROM [tb] GROUP BY [col1] ,[col2] ,[col3] ORDER BY [col1] ,[col2] ,[col3] |
但是mysql没有上面的要求,如SELECT * FROM account_withdraw_records GROUP BY STATUS可以正常执行。