MYSQL中遇到的问题以及解决方法(二)排序后筛选
MYSQL中没有row_number()over(partition by order by ),如何排序后进行筛选?
方法一:
select OperatorID,sum(cnt) as '总人数',sum(case when Quality=1 then cnt else 0 end ) as '质量合格的人数', sum(case when Quality=-1 then cnt else 0 end ) as '质量一般的人数', sum(case when Quality=-2 then cnt else 0 end ) as '质量差的人数', sum(case when Quality=-3 then cnt else 0 end ) as '血压可疑的人数' from (select OperatorID,Quality,count(0)cnt from ( select * from (select * from (SELECT p.DataID,p.Quality,p.PatientID , p.PPGFilePath, p.SBP, p.DBP, pa.Age, pa.Sex, pa.Height, pa.Weight,round( pa.BMI,2)BMI,p.ECGFilePath, p.OperatorID FROM ppg_data AS p LEFT JOIN (select *,pa.Weight/power(pa.Height,2)*10000 as BMI from patient AS pa)pa ON p.PatientID=pa.PatientID LEFT JOIN operator AS o ON o.OperatorID = p.OperatorID WHERE p.TestEndTime >= '2020-06-01 00:00:00' AND p.TestEndTime < '2020-07-01 00:00:00' AND pa.RecordIsDeleted = 0 AND o.isCensus = 1 AND p.OperatorID IN ('107','112','113','114','116','117','132','134','135','145','146','147','149','154','153') AND p.PPGFilePath IS NOT NULL AND p.SBP IS NOT NULL ORDER BY p.PatientID)as p order by p.Quality desc,DataID desc limit 20000)p where (p.OperatorID,p.PatientID) IN (select p.OperatorID,p.PatientID FROM patient p group by p.OperatorID,p.PatientID) group by p.PatientID ,p.OperatorID)as a group by OperatorID,Quality)A group by OperatorID;
可以先进行排序后limit,必须进行limit,而且limit必须要大于真实的条数,后再进行group by 就会选择第一条数据,那么如何选择前两条呢?貌似这个方法解决不了?
方法二:
select OperatorID,sum(cnt) as '总人数',sum(case when Quality=1 then cnt else 0 end ) as '质量合格的人数', sum(case when Quality=-1 then cnt else 0 end ) as '质量一般的人数', sum(case when Quality=-2 then cnt else 0 end ) as '质量差的人数', sum(case when Quality=-3 then cnt else 0 end ) as '血压可疑的人数' from (select OperatorID,Quality,count(0)cnt from ( select * from (select a.*,IF (@p=PatientID,@r:=@r+1,@r:=1) as rank,@p:=PatientID from (SELECT p.DataID,p.Quality,p.PatientID , p.PPGFilePath, p.SBP, p.DBP, pa.Age, pa.Sex, pa.Height, pa.Weight,round( pa.BMI,2)BMI,p.ECGFilePath, p.OperatorID FROM ppg_data AS p LEFT JOIN (select *,pa.Weight/power(pa.Height,2)*10000 as BMI from patient AS pa)pa ON p.PatientID=pa.PatientID LEFT JOIN operator AS o ON o.OperatorID = p.OperatorID WHERE p.TestEndTime >= '2020-06-01 00:00:00' AND p.TestEndTime < '2020-07-01 00:00:00' AND pa.RecordIsDeleted = 0 AND o.isCensus = 1 AND p.OperatorID IN ('107','112','113','114','116','117','132','134','135','145','146','147','149','154','153') AND (p.OperatorID,p.PatientID) IN (select p.OperatorID,p.PatientID FROM patient p group by p.OperatorID,p.PatientID) AND p.KRKPPGFilePath IS NOT NULL AND p.SBP IS NOT NULL ORDER BY p.PatientID)a,(select @p:=NUll,@r:=0)r ORDER BY PatientID,Quality desc,DataID desc)A where rank=1)A group by OperatorID,Quality)A group by OperatorID;
rank=1就是选择第一条, <3就是前两条
对于按照多个字段进行分组的,需要将IF后面的条件用and连接即可。@p=PatientID and @s=TestEndTime
参考链接: https://www.cnblogs.com/niniya/p/9046449.html