对于sql server来说:
GROUP BY和ORDER BY同时存在的情况是,ORDER BY对GROUP BY后的结果再进行排序的,所以ORDER BY后面的排序字段需要在SELECT里出现的,即ORDER
BY
子句中的列必须包含在聚合函数或
GROUP
BY
子句中。
如:
下面这个就是错的
1
|
SELECT
[col1] ,[col2]
FROM
[tb]
GROUP
BY
[col1] ,[col2]
ORDER
BY
[col1] ,[col2] ,[col3]
|
而下面的都是对的
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可以正常执行。