MYSQL中遇到的问题以及解决方法（三）横转纵
时间：2021-07-01 10:21:17
帮助过：23人阅读
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 (
select p.PatientID , p.KRKPPGFilePath, p.SBP, p.DBP, pa.Age, pa.Sex, pa.Height, pa.Weight,pa.BMI,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.PatientID
in (
select PatientID
from (
SELECT
p.PatientID , count(
0)
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 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.Quality
=1
AND p.KRKPPGFilePath
IS NOT NULL
AND p.SBP
IS NOT NULL
group by p.PatientID
having 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.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)a,(
select @p:
=NUll,
@r:
=0)r)a
where rank
<3)A
group by PatientID , Age, Sex, Height, Weight,BMI
文本类的数据也是可以添加聚合函数的，PPGFilePath是一推数字和字母组合在一起的，和NULL取最大仍然也是可以取出来的。
MYSQL中遇到的问题以及解决方法（三）横转纵
标签：The case HERE color tor record else path 遇到的问题
