mysql扩展了标准sql的几点,觉得确实有用:
1.group by 后面的栏位可以不出现在projection中。
select firstname ,lastname,max(p_id)
from person group by firstname ;
开发中会遇到:根据col1分组,col2聚集,对应的那一笔数据例如:
select
firstname,
age,
min(age)
from
person
group by firstname
这种问题都可以通过子查询来组合,oracle可以通过分析和聚集一起使用来达到目的。
伪代码:
select col1,min()over(partition by... order by col1),col3
from table group by col1;
2.group by 后面可以是alias
select firstname ,lastname,max(p_id)
from person group by firstname ;
同样having中也可以用alias
select firstname ,max(age) as c
from person group by firstname
where c = 30
3.order by 后面可以是非聚集栏位
select
firstname,
min(age)
from
person
group by firstname order by lastname;
4.文档中提到可以跟表达式,oracle中这个不是个事,不晓得标准sql是怎么样的。