mysql工作中遇到的问题_MYSQL中遇到的问题以及解决方法（三）横转纵

PatientID , Age, Sex, Height, Weight,OperatorID,BMI,max(case when rank%2=1 then KRKPPGFilePath else ‘‘ end) KRKPPGFilePath ,max(case when rank%2=1 then SBP else ‘‘ end) SBP,max(case when rank%2=1 then DBP else ‘‘ end) DBP,max(case when rank%2=0 then KRKPPGFilePath else ‘‘ end) KRKPPGFilePath1 ,max(case when rank%2=0 then SBP else ‘‘ end) SBP1,max(case when rank%2=0 then DBP else ‘‘ end) DBP1 from(select PatientID , KRKPPGFilePath, SBP, DBP, Age, Sex, Height, Weight,round(BMI,2)BMI,OperatorID,rank from (select a.*,IF (@p=PatientID,@r:=@r+1,@r:=1) as rank,@p:=PatientID from (selectp.PatientID , p.KRKPPGFilePath, p.SBP, p.DBP, pa.Age, pa.Sex, pa.Height, pa.Weight,pa.BMI,p.OperatorIDFROMppg_dataASpLEFT JOIN(select *,pa.Weight/power(pa.Height,2)*10000 as BMI from patient AS pa)pa ON p.PatientID=pa.PatientIDLEFT JOIN operator AS o ON o.OperatorID =p.OperatorIDwhere p.PatientID in(select PatientID from ( SELECTp.PatientID ,count(0)FROMppg_dataASpLEFT JOIN(select *,pa.Weight/power(pa.Height,2)*10000 as BMI from patient AS pa)pa ON p.PatientID=pa.PatientIDLEFT JOIN operator AS o ON o.OperatorID =p.OperatorIDWHEREp.TestEndTime>= ‘2019-12-17 00:00:01‘

AND p.TestEndTime < ‘2020-07-08 00:00:01‘

AND p.State = ‘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.DBPAND BMI>=15

AND BMI<=50

AND pa.RecordIsDeleted = 0

AND o.isCensus = 1

and p.Quality=1

AND p.KRKPPGFilePath IS NOT NULL

AND p.SBP IS NOT NULL

group byp.PatientIDhaving count(0)=1)A)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.DBPAND 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)a,(select @p:=NUll,@r:=0)r)awhere rank<3)Agroup by PatientID , Age, Sex, Height, Weight,BMI

文本类的数据也是可以添加聚合函数的，PPGFilePath是一推数字和字母组合在一起的，和NULL取最大仍然也是可以取出来的。

MYSQL中遇到的问题以及解决方法(三)横转纵

标签：The   case   HERE   color   tor   record   else   path   遇到的问题

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

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