1.需求:
取出下表,每个日期当中rksj最大对应的记录值,组成新的表数据。
2.解决方案:
解决过程中,使用到了group_concat() 函数,group_concat( 参数1,[参数2] ,[参数3] )
参数1,即要拼接的字段;参数2,排序字段;参数3,默认拼接字符。
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
-- 方法一
-- group_concat() 即要拼接的字段,排序字段,默认拼接字符
select t1.* from view_dcqd_result t1 inner join
(select SUBSTRING_INDEX(group_concat(id order by rksj desc),',',1) as id
from view_dcqd_result GROUP BY date ) as t2 on t2.id = t1.id
;
-- 方法二
-- group_concat() 字段优化
select t1.* from view_dcqd_result t1
inner join (select SUBSTRING_INDEX(group_concat(rksj order by rksj desc),',',1) as rksj
from view_dcqd_result GROUP BY date ) as t2 on t2.rksj = t1.rksj
;
-- 方法三
-- 分组,最大值
select t1.* from view_dcqd_result t1 inner join
(select date,max(rksj) as rksj from view_dcqd_result GROUP BY date) t2
on t2.rksj = t1.rksj