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

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 行列转换内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！