当前位置:Gxlcms > 数据库问题 > MYSQL中遇到的问题以及解决方法(四)排序后筛选+横转纵

MYSQL中遇到的问题以及解决方法(四)排序后筛选+横转纵

时间:2021-07-01 10:21:17 帮助过:10人阅读

KRK 和YD组合到一起 select PatientID , Age, Sex, Height, Weight,OperatorID,BMI, max(case when KRKPPGFilePath is not null then KRKPPGFilePath else ‘‘ end) KRKPPGFilePath , max(case when KRKPPGFilePath is not null then SBP else ‘‘ end) KRK_SBP, max(case when KRKPPGFilePath is not null then DBP else ‘‘ end) KRK_DBP, max(case when PPGFilePath is not null then PPGFilePath else ‘‘ end) PPGFilePath1 , max(case when PPGFilePath is not null then SBP else ‘‘ end) YD_SBP, max(case when PPGFilePath is not null then DBP else ‘‘ end) YD_DBP, max(case when PPGFilePath is not null then Ecgdisease else ‘‘ end) Ecgdisease, max(case when PPGFilePath is not null then EcgHRV else ‘‘ end) EcgHRV from ( select p.PatientID ,p.PPGFilePath, p.KRKPPGFilePath, p.SBP, p.DBP, pa.Age, pa.Sex, pa.Height, pa.Weight,pa.BMI,p.OperatorID,p.Ecgdisease,p.EcgHRV 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.PatientID not IN (SELECT PatientID from patient_fortest) and p.PatientID in ( select A.PatientID 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 >= 2019-12-17 00:00:01 AND p.TestEndTime < 2020-07-08 00:00:01 AND p.State = 1 AND p.Quality=1 AND pa.Age>=16 AND pa.Age<=120 AND pa.Weight>=30 AND pa.Weight<=160 AND pa.Height>=130 AND pa.Height<=220 AND p.SBP>p.DBP AND BMI>=15 AND BMI<=50 AND pa.RecordIsDeleted = 0 AND o.isCensus = 1 AND p.KRKPPGFilePath IS NOT NULL AND p.SBP IS NOT NULL ORDER BY p.PatientID)as p order by p.Quality desc,DataID desc limit 30000)p group by p.PatientID )A join (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 >= 2019-12-17 00:00:01 AND p.TestEndTime < 2020-07-08 00:00:01 AND p.State = 1 AND p.Quality=1 AND pa.Age>=16 AND pa.Age<=120 AND pa.Weight>=30 AND pa.Weight<=160 AND pa.Height>=130 AND pa.Height<=220 AND p.SBP>p.DBP AND BMI>=15 AND BMI<=50 AND pa.RecordIsDeleted = 0 AND o.isCensus = 1 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 30000)p group by p.PatientID )B on A.PatientID=B.PatientID ) and p.TestEndTime >= 2019-12-17 00:00:01 AND p.TestEndTime < 2020-07-08 00:00:01 AND p.State = 1 and p.Quality=1 AND pa.Age>=16 AND pa.Age<=120 AND pa.Weight>=30 AND pa.Weight<=160 AND pa.Height>=130 AND pa.Height<=220 AND p.SBP>p.DBP AND BMI>=15 AND BMI<=50 AND pa.RecordIsDeleted = 0 AND o.isCensus = 1 AND p.SBP IS NOT NULL ORDER BY p.PatientID)A group by PatientID , Age, Sex, Height, Weight,BMI

 

MYSQL中遇到的问题以及解决方法(四)排序后筛选+横转纵

标签:sde   des   time   not   mit   gdi   max   方法   lse   

人气教程排行