mysql 分组统计 取最大值_server sql 、MySQL 分组查询取最大值

---server sql

SELECT * FROM (

select fund_code,fundsname,invst_type,fund_status,

ROW_NUMBER() OVER(PARTITION BY FUND_CODE ORDER BY ESTAB_DATE DESC) AS RN

from pgenius.DBO.fnd_gen_info

where isvalid=1 and trade_mkt is null ) A

WHERE RN=1

--mysql

select * from (select * from pgenius.fnd_gen_info where isvalid=1 and trade_mkt is null ORDER BY ESTAB_DATE desc ) t group by FUND_CODE

这个有点坑，实际排序没有生效，取的是主键的最小的记录对应的整条记录

要优化

select t.* from (

select distinct(FUND_CODE) as tt,t1.* from pgenius.fnd_gen_info as t1

where isvalid=1 and trade_mkt is null order by estab_date desc

) as t group by FUND_CODE \G;

----and FUND_CODE='001064'

以下是转来的详细分析过程，留着备用

mysql 分组后再获取组内排序的第一条数据

如图，如果我们按 number 分组后再按 is_bak 从大到小排序获取最大的第一条数据，那么最后结果就是 id 为 1，4，6 这 3 条。

select t.* from (

select distinct(id) as tt,t1.* from a_t as t1 order by is_bak desc

) as t group by number order by id asc;

1

2

3