• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

mysql 行列转换的示例代码

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

一、需求

我们有三张表,我们需要分类统计一段时间内抗生素的不同药敏结果,即 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

2、按照项目、抗生素、药敏结果求出不同药敏结果数量

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  

3、将两个结果关联到一起

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.`检出总数`<>''

4、一般来说,到上一步不同药敏数量和总数都有了,可以直接求比例了

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

我们借助于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.`检出总数`<>''                                        
) C
GROUP BY C.project_name,C.antibiotic_dict_name;

5、查看结果,成功转换


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


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
Redis教程(二):String数据类型发布时间:2022-02-10
下一篇:
oracle如何解锁封锁的账号发布时间:2022-02-08
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap