mysql中行列转换(mysql 行列转换的示例代码)
类别:数据库 浏览量:397
时间:2021-10-21 07:09:41 mysql中行列转换
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 行列转换内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/kk_gods/article/details/111933336
您可能感兴趣
- idea怎样连接mysql(IDEA配置连接MYSQL数据库遇到Failed这个问题解决)
- mysql编码设置
- mysql字符集怎么看(mysql字符集相关总结)
- mysql 触发器是什么(MySQL触发器的使用)
- mysql 安装阿里云(详解如何在阿里云服务器安装Mysql数据库)
- mysql exists的用法(Mysql exists用法小结)
- 最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)(最新版MySQL 8.0.22下载安装超详细教程Windows 64位)
- linuxmysql安装教程5.7.25学习(linux mysql5.5升级至mysql5.7的步骤与踩到的坑)
- mysql拼接多字段作为查询条件(Mysql 实现字段拼接的三个函数)
- mysql常见的存储引擎(如何选择MySQL的存储引擎?)
- mysql是自动commit吗(详解MySQL与Spring的自动提交autocommit)
- mysql 加锁处理分析(mysql死锁和分库分表问题详解)
- mysql是否支持透明数据加密(MySQL的加密解密的几种方式小结)
- mysqlworkbench怎么设置连接(详解MySQL Workbench使用教程)
- mysql的binlog几种模式(MySQL系列之redo log、undo log和binlog详解)
- ubuntu下mysql安装教程(Ubuntu 20.04 安装和配置MySql5.7的详细教程)
- 九儿《狐踪谍影》出演热血女特警,戏份杀青受关注(九儿狐踪谍影出演热血女特警)
- 红色代表什么(红色代表什么寓意)
- 蓝天代表什么(蓝天代表什么生肖)
- 今天要吃什么(今天要吃什么菜)
- 营养餐是什么(学校营养餐是什么)
- 谁说女子不如男 范冰冰演的武则天只是其一,另外两位你认识吗(谁说女子不如男)
热门推荐
- 通过接口访问mysql数据库(使用mysql记录从url返回的http GET请求数据操作)
- extjs checkboxGroup 复选框的用法
- pythonlogging模块教学(详解Python logging调用Logger.info方法的处理过程)
- 宝塔面板与php(宝塔面板如何安装PHP扩展)
- mysql数据表的创建与管理(MySQL数据操作-DML语句的使用)
- ASP.NET常用的代码收集
- dedecms如何使用标签(dedecms模板标签如何做判断示例代码)
- docker 启动rabbitmq(docker安装rabbitmq无法进入管理页面的问题)
- php换行乱码(php输出文字乱码的解决方法)
- mysqljson字段查询(Mysql 查询JSON结果的相关函数汇总)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9