MYSQL中遇到的问题以及解决方法(五)分组统计数据
select DBP_Sec+0 as ID ,ELT( INTERVAL (DBP_sec, 1, 2, 3, 4,5,6,7,8,9,10,11), "0-50", "50-60", "60-70", "70-80", "80-90", "90-100", "100-110", "110-120", "120-130", "130-140", "140+") as DBP,cnt,cnt/total_cnt*100 as percentage from ( SELECT ELT( INTERVAL (DBP, 0, 50, 60,70,80, 90,100,110,120,130,140), "1","2","3","4","5","6","7","8","9","10","11" ) AS DBP_sec, COUNT(0) AS cnt FROM (SELECT p.*, pa.Age, pa.Sex, pa.Height, pa.Weight,pa.OperatorID as PaoperatorID,o.isCensus FROM ppg_data AS p LEFT JOIN patient AS 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-05-15 00:00:01' AND p.State = '1' AND o.isCensus = 1 AND SBP IS not null AND p.PPGFilePath IS NOT NULL)A GROUP BY ELT( INTERVAL (DBP, 0, 50, 60,70,80, 90,100,110,120,130,140), "1","2","3","4","5","6","7","8","9","10","11") order by DBP_sec)A join (select count(0) as total_cnt FROM ppg_data AS p LEFT JOIN patient AS 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-05-15 00:00:01' AND p.State = '1' AND o.isCensus = 1 AND SBP IS not null AND p.PPGFilePath IS NOT NULL)B on 1=1 order by ID;
结果:左闭右开