MYSQL中遇到的问题以及解决方法（二）排序后筛选

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL中遇到的问题以及解决方法（二）排序后筛选，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3297字，纯文字阅读大概需要5分钟。

内容图文

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就是前两条

参考链接： https://www.cnblogs.com/niniya/p/9046449.html

MYSQL中遇到的问题以及解决方法（二）排序后筛选

标签：logs   第一条   end   font   rom   code   mysq   let   data   

本文系统来源：https://www.cnblogs.com/zhanghongpan/p/13275402.html

内容总结

以上是互联网集市为您收集整理的MYSQL中遇到的问题以及解决方法（二）排序后筛选全部内容，希望文章能够帮你解决MYSQL中遇到的问题以及解决方法（二）排序后筛选所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---