请选择 进入手机版 | 继续访问电脑版

mysql 行列转换

[复制链接]
太阳神鹰 发表于 2021-1-1 10:31:12 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
一、需求
我们有三张表,我们需要分类统计一段时间内抗生素的差异药敏效果,即 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、检察效果,乐成转换

;

来源:https://blog.csdn.net/kk_gods/article/details/111933336
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )