在SQL中使用GROUP BY时,有一些事必须注意:
1。不能使用别名
2。除了函数字段,SELECT出现的所有字段都必须在GROUP BY中出现
3。别名不要使用保留字
不同的数据库要求不一样,MYSQL没有这些限制,SQL SERVER有1,2的限制,ORACLE有1,2,3的限制。
比如以下几个都不对:
select count(*), to_char(TS,'YYYYMMDD') as date, protocol_name from TRANSACTIONS where PROTOCOL_NAME = 'cXML' group by date, PROTOCOL_NAME order by date
select count(*), to_char(TS,'YYYYMMDD') as date, protocol_name from TRANSACTIONS where PROTOCOL_NAME = 'cXML' group by to_char(TS,'YYYYMMDD') order by date
select count(*), to_char(TS,'YYYYMMDD') as date, protocol_name from TRANSACTIONS where PROTOCOL_NAME = 'cXML' group by to_char(TS,'YYYYMMDD'), PROTOCOL_NAME order by date
这个才对:
select count(*), to_char(TS,'YYYYMMDD') as t_date, protocol_name from TRANSACTIONS where PROTOCOL_NAME = 'cXML' group by to_char(TS,'YYYYMMDD'), PROTOCOL_NAME order by t_date
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。