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
