在进行一张表进行groupby后获取最大id和最小id数据的sql语句

时间:2022-03-22 20:10:04

在进行一张表进行groupby后获取最大id和最小id数据的sql语句; 方法一:变量 select result.* from (           select heyf_tmp.*,@rownum:=@rownum+1 as rownum,                if(@pdept=heyf_tmp.id,@rank:=@rank+1,@rank:=1) as rank,                @pdept:=heyf_tmp.id as id1 from ( select distinct bpt.id, spi.userid,spi.startTime,spi.endTime,spi.orgId,spi.projectCode,spi.endTime finishtime,1 as result,IFNULL(bpt.planName,'日常巡查') planName,su.nickname nickname,bwp.`name` projectCodeName from bus_patrol_info spi  left join bus_patrol_task bpt on spi.orgid=bpt.orgid and bpt.result=1   left join sys_user su on spi.userId = su.id  left JOIN bus_water_project bwp on bwp.`code` = bpt.projectCode  where 1=1 AND spi.orgId = "372" and spi.userId = bpt.userId order by bpt.id,spi.startTime desc) heyf_tmp ,     (select @rownum :=0 , @pdept := null ,@rank:=0) a  ) result where rank=1
方法为: MAX(id)-》 GROUP_CONCAT(a.id)-》FIND_IN_SET SELECT a.waterlevel,IFNULL(1/IFNULL(1/(max(flow)-min(flow)),0),0) flow,time FROM(select AVG(waterlevel) waterlevel,flow,time from bus_waterregime GROUP BY  DATE_FORMAT(time,'%Y-%m-%d %H') UNIONSELECT 'NaAVG' waterlevel,flow,time FROM bus_waterregime b where FIND_IN_SET(b.id,(SELECT GROUP_CONCAT(a.id) id FROM(select MAX(id) id from bus_waterregime GROUP BY  DATE_FORMAT(time,'%Y-%m-%d %H'))a)))a GROUP BY DATE_FORMAT(time,'%Y-%m-%d %H')
在公司有限的数据中两者的查询时间差不多,如有兴趣者可以一试两者的性能如何