mysql 行列转换的示例代码

一、需求

我们有三张表，我们需要分类统计一段时间内抗生素的不同药敏结果，即 report_item_drugs 表的 drugs_result，在不同项目project_name 和不同抗生素 antibiotic_dict_name 下的占比，并将药敏结果显示在行上，效果如下：

三张原始表（仅取需要的字段示例），分别是：

报告表

项目表

抗生素表（药敏结果drugs_result为一列值）

二、实现

1、按照项目、抗生素分组求出检出的总数

SELECT  A.project_name,A.antibiotic_dict_name,SUM(nums) AS 检出总数FROM (      SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r       RIGHT JOIN report_item i ON r.id=i.report_id       RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id       WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30'        GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result )  A GROUP BY A.project_name,A.antibiotic_dict_name

SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, '未填写') AS drugs_result,COUNT(d.id) AS 数量 FROM `report` rRIGHT JOIN report_item i ON r.id=i.report_idRIGHT JOIN report_item_drugs d ON d.report_item_id=i.idWHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result

SELECT       BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`数量`,AA.`检出总数`    FROM         (              SELECT                 A.project_name,A.antibiotic_dict_name,SUM(nums) AS 检出总数              FROM               (                    SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r                    RIGHT JOIN report_item i ON r.id=i.report_id                    RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id                    WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30'                     GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result              )  A              GROUP BY A.project_name,A.antibiotic_dict_name        ) AA         RIGHT JOIN         (              SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, '未填写') AS drugs_result,COUNT(d.id) AS 数量               FROM `report` r              RIGHT JOIN report_item i ON r.id=i.report_id              RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id              WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30'               GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result                    )BB ON AA.project_name=BB.project_name AND AA.antibiotic_dict_name=BB.antibiotic_dict_name    WHERE AA.`检出总数`<>''

但是，我们需要的是将药敏显示到行上，直接求比不符合需求，所以我们需要将列转换为行

我们借助于case when实现行列转换，并将药敏结果根据字典转为方便阅读的汉字

SELECT  C.project_name 项目名称,C.antibiotic_dict_name 抗生素名称,C.`检出总数`,  SUM(CASE C.`drugs_result` WHEN 'D' THEN C.`数量` ELSE 0 END ) AS '剂量依赖性敏感',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'D' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '剂量依赖性敏感比率',  SUM(CASE C.`drugs_result` WHEN 'R' THEN C.`数量` ELSE 0 END ) AS '耐药',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'R' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '耐药比率',  SUM(CASE C.`drugs_result` WHEN 'S' THEN C.`数量` ELSE 0 END ) AS '敏感',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'S' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '敏感比率',  SUM(CASE C.`drugs_result` WHEN 'I' THEN C.`数量` ELSE 0 END ) AS '中介',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'I' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '中介比率',  SUM(CASE C.`drugs_result` WHEN 'n1' THEN C.`数量` ELSE 0 END ) AS '非敏感',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'n1' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '非敏感比率',  SUM(CASE C.`drugs_result` WHEN 'N' THEN C.`数量` ELSE 0 END ) AS '无',  CONCAT(SUM(CASE C.`drugs_result` WHEN 'N' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '无比率',  SUM(CASE C.`drugs_result` WHEN '未填写' THEN C.`数量` ELSE 0 END ) AS '未填写',  CONCAT(SUM(CASE C.`drugs_result` WHEN '未填写' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '未填写比率'FROM(    SELECT       BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`数量`,AA.`检出总数`    FROM         (              SELECT                 A.project_name,A.antibiotic_dict_name,SUM(nums) AS 检出总数              FROM               (                    SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r                    RIGHT JOIN report_item i ON r.id=i.report_id                    RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id                    WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30'                     GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result              )  A              GROUP BY A.project_name,A.antibiotic_dict_name        ) AA         RIGHT JOIN         (              SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, '未填写') AS drugs_result,COUNT(d.id) AS 数量               FROM `report` r              RIGHT JOIN report_item i ON r.id=i.report_id              RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id              WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30'               GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result                    )BB ON AA.project_name=BB.project_name AND AA.antibiotic_dict_name=BB.antibiotic_dict_name    WHERE AA.`检出总数`<>''                                        ) CGROUP BY C.project_name,C.antibiotic_dict_name;

到此这篇关于mysql 行列转换的示例代码的文章就介绍到这了,更多相关mysql 行列转换内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

一、需求

二、实现

更多相关文章

随机推荐