MySQL 行列转换的实现方法
2022-11-12 09:12:32
内容摘要
这篇文章主要为大家详细介绍了MySQL 行列转换的实现方法,具有一定的参考价值,可以用来参考一下。
感兴趣的小伙伴,下面一起跟随512笔记的小玲来看看吧!
一、需求
我们有三张表,
文章正文
这篇文章主要为大家详细介绍了MySQL 行列转换的实现方法,具有一定的参考价值,可以用来参考一下。
感兴趣的小伙伴,下面一起跟随数据库教程的小编来看看吧!
一、需求
我们有三张表,我们需要分类统计一段时间内抗生素的不同药敏结果,即 report_item_drugs 表的 drugs_result, 在不同项目project_name 和不同抗生素 antibiotic_dict_name 下的占比,并将药敏结果显示在行上,效果如下:
三张原始表(仅取需要的字段示例),分别是:
报告表
项目表
抗生素表(药敏结果drugs_result为一列值)
二、实现
1、按照项目、抗生素分组求出检出的总数
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <code> 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 </code> |
MySQL 行列转换的示例代码
2、按照项目、抗生素、药敏结果求出不同药敏结果数量
代码如下:
1 2 3 4 5 6 7 8 9 | <code> 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 </code> |
MySQL 行列转换的示例代码
3、将两个结果关联到一起
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | <code> 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.`检出总数`<> '' </code> |
MySQL 行列转换的示例代码
4、一般来说,到上一步不同药敏数量和总数都有了,可以直接求比例了
但是,我们需要的是将药敏显示到行上,直接求比不符合需求,所以我们需要将列转换为行
我们借助于case when实现行列转换,并将药敏结果根据字典转为方便阅读的汉字
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | <code> 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.`检出总数`<> '' ) C GROUP BY C.project_name,C.antibiotic_dict_name;</code> |
MySQL 行列转换的示例代码
5、查看结果,成功转换
到此这篇关于mysql 行列转换的示例代码的文章就介绍到这了,更多相关mysql 行列转换内容请搜索512笔记以前的文章或继续浏览下面的相关文章希望大家以后多多支持512笔记!
注:关于MySQL 行列转换的实现方法的内容就先介绍到这里,更多相关文章的可以留意
代码注释