mysql 行列轉(zhuǎn)換的示例代碼
發(fā)布日期：2022-02-15 09:47 | 文章來(lái)源：源碼之家
一、需求
我們有三張表，我們需要分類統(tǒng)計(jì)一段時(shí)間內(nèi)抗生素的不同藥敏結(jié)果，即 report_item_drugs 表的 drugs_result， 在不同項(xiàng)目project_name 和不同抗生素 antibiotic_dict_name 下的占比，并將藥敏結(jié)果顯示在行上，效果如下：
三張?jiān)急恚▋H取需要的字段示例），分別是：
報(bào)告表
項(xiàng)目表
抗生素表（藥敏結(jié)果drugs_result為一列值）
二、實(shí)現(xiàn)
1、按照項(xiàng)目、抗生素分組求出檢出的總數(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、按照項(xiàng)目、抗生素、藥敏結(jié)果求出不同藥敏結(jié)果數(shù)量
SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, '未填寫(xiě)') 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、將兩個(gè)結(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, '未填寫(xiě)') 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、一般來(lái)說(shuō)，到上一步不同藥敏數(shù)量和總數(shù)都有了，可以直接求比例了
但是，我們需要的是將藥敏顯示到行上，直接求比不符合需求，所以我們需要將列轉(zhuǎn)換為行
我們借助于case when實(shí)現(xiàn)行列轉(zhuǎn)換，并將藥敏結(jié)果根據(jù)字典轉(zhuǎn)為方便閱讀的漢字
SELECT
C.project_name 項(xiàng)目名稱,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 '無(wú)',
CONCAT(SUM(CASE C.`drugs_result` WHEN 'N' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '無(wú)比率',
SUM(CASE C.`drugs_result` WHEN '未填寫(xiě)' THEN C.`數(shù)量` ELSE 0 END ) AS '未填寫(xiě)',
CONCAT(SUM(CASE C.`drugs_result` WHEN '未填寫(xiě)' THEN FORMAT(C.`數(shù)量`/C.`檢出總數(shù)`*100,2) ELSE 0 END),'%') AS '未填寫(xiě)比率'
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, '未填寫(xiě)') 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)換
到此這篇關(guān)于mysql 行列轉(zhuǎn)換的示例代碼的文章就介紹到這了,更多相關(guān)mysql 行列轉(zhuǎn)換內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信