服务器之家

服务器之家 > 正文

mysql 行列转换的示例代码

时间:2021-04-27 21:16     来源/作者:看,月亮在跳舞

一、需求

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

mysql 行列转换的示例代码

三张原始表(仅取需要的字段示例),分别是:

报告表

mysql 行列转换的示例代码

项目表

mysql 行列转换的示例代码

抗生素表(药敏结果drugs_result为一列值)

mysql 行列转换的示例代码

二、实现

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

?
1
2
3
4
5
6
7
8
9
10
11
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

mysql 行列转换的示例代码

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

?
1
2
3
4
5
6
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 

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

mysql 行列转换的示例代码

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

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

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

mysql 行列转换的示例代码

?
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
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 行列转换的示例代码的文章就介绍到这了,更多相关mysql 行列转换内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://blog.csdn.net/kk_gods/article/details/111933336

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
返回顶部