mysql之group by聚集扩展

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是怎么样的。