快捷導(dǎo)航

mysql 行列轉(zhuǎn)換的示例代碼

更新時間：2021年03月24日 09:05:03 作者：看，月亮在跳舞

這篇文章主要介紹了mysql 行列轉(zhuǎn)換的示例代碼,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、需求

我們有三張表，我們需要分類統(tǒng)計一段時間內(nèi)抗生素的不同藥敏結(jié)果，即 report_item_drugs 表的 drugs_result，在不同項目project_name 和不同抗生素 antibiotic_dict_name 下的占比，并將藥敏結(jié)果顯示在行上，效果如下：

三張原始表（僅取需要的字段示例），分別是：

報告表

項目表

抗生素表（藥敏結(jié)果drugs_result為一列值）

二、實現(xiàn)

1、按照項目、抗生素分組求出檢出的總數(shù)

SELECT 
 A.project_name,A.antibiotic_dict_name,SUM(nums) AS 檢出總數(shù)
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、按照項目、抗生素、藥敏結(jié)果求出不同藥敏結(jié)果數(shù)量

SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, '未填寫') AS drugs_result,COUNT(d.id) AS 數(shù)量 
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、將兩個結(jié)果關(guān)聯(lián)到一起

SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`數(shù)量`,AA.`檢出總數(shù)`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS 檢出總數(shù)
              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 數(shù)量 
              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.`檢出總數(shù)`<>''

4、一般來說，到上一步不同藥敏數(shù)量和總數(shù)都有了，可以直接求比例了

但是，我們需要的是將藥敏顯示到行上，直接求比不符合需求，所以我們需要將列轉(zhuǎn)換為行

我們借助于case when實現(xiàn)行列轉(zhuǎn)換，并將藥敏結(jié)果根據(jù)字典轉(zhuǎn)為方便閱讀的漢字

SELECT
  C.project_name 項目名稱,C.antibiotic_dict_name 抗生素名稱,C.`檢出總數(shù)`,
  SUM(CASE C.`drugs_result` WHEN 'D' THEN C.`數(shù)量` ELSE 0 END ) AS '劑量依賴性敏感',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'D' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '劑量依賴性敏感比率',
  SUM(CASE C.`drugs_result` WHEN 'R' THEN C.`數(shù)量` ELSE 0 END ) AS '耐藥',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'R' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '耐藥比率',
  SUM(CASE C.`drugs_result` WHEN 'S' THEN C.`數(shù)量` ELSE 0 END ) AS '敏感',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'S' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '敏感比率',
  SUM(CASE C.`drugs_result` WHEN 'I' THEN C.`數(shù)量` ELSE 0 END ) AS '中介',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'I' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '中介比率',
  SUM(CASE C.`drugs_result` WHEN 'n1' THEN C.`數(shù)量` ELSE 0 END ) AS '非敏感',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'n1' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '非敏感比率',
  SUM(CASE C.`drugs_result` WHEN 'N' THEN C.`數(shù)量` ELSE 0 END ) AS '無',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'N' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '無比率',
  SUM(CASE C.`drugs_result` WHEN '未填寫' THEN C.`數(shù)量` ELSE 0 END ) AS '未填寫',
  CONCAT(SUM(CASE C.`drugs_result` WHEN '未填寫' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '未填寫比率'
FROM
(
    SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`數(shù)量`,AA.`檢出總數(shù)`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS 檢出總數(shù)
              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 數(shù)量 
              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.`檢出總數(shù)`<>''                                        
) C
GROUP BY C.project_name,C.antibiotic_dict_name;

5、查看結(jié)果，成功轉(zhuǎn)換