mysql row column conversion sample code

1. Demand

We have three tables. We need to classify and count the different antibiotic sensitivity results over a period of time, that is, the drugs_result of the report_item_drugs table, the proportion under different projects project_name and different antibiotics antibiotic_dict_name, and display the drug sensitivity results on the rows . The effect is as follows:

The three original tables (only the required fields are taken as examples) are:

Report Form

Project List

Antibiotics table (drug sensitivity results drugs_result is a column of values)

2. Implementation

1. Calculate the total number of detected cases by project and antibiotic group

SELECT 
 A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections 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. Calculate the number of different drug sensitivity results according to the project, antibiotic, and drug sensitivity results

SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity 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. Link the two results together

SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`quantity`,AA.`total number of detections`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections 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, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity 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.`Total number of detections`<>'

4. Generally speaking, the number of different drug sensitivity and the total number are available in the previous step, so the ratio can be directly calculated.

However, what we need is to display the drug sensitivity on the row, and directly calculating the ratio does not meet the requirements, so we need to convert the column into a row

We use case when to convert rows and columns, and convert the drug sensitivity results into easy-to-read Chinese characters according to the dictionary

SELECT
  C.project_name project name, C.antibiotic_dict_name antibiotic name, C.`total number of detections`,
  SUM(CASE C.`drugs_result` WHEN 'D' THEN C.`Quantity` ELSE 0 END ) AS 'Dose-dependent sensitivity',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'D' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Dose-dependent sensitivity ratio',
  SUM(CASE C.`drugs_result` WHEN 'R' THEN C.`Quantity` ELSE 0 END ) AS 'Drug resistance',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'R' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Drug resistance ratio',
  SUM(CASE C.`drugs_result` WHEN 'S' THEN C.`Quantity` ELSE 0 END ) AS 'Sensitive',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'S' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Sensitive ratio',
  SUM(CASE C.`drugs_result` WHEN 'I' THEN C.`Quantity` ELSE 0 END ) AS 'Agency',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'I' THEN FORMAT(C.`Quantity`/C.`Total Detection`*100,2) ELSE 0 END),'%') AS 'Intermediary Ratio',
  SUM(CASE C.`drugs_result` WHEN 'n1' THEN C.`quantity` ELSE 0 END ) AS 'non-sensitive',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'n1' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Non-sensitive ratio',
  SUM(CASE C.`drugs_result` WHEN 'N' THEN C.`Quantity` ELSE 0 END ) AS 'None',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'N' THEN FORMAT(C.`Quantity`/C.`Total Detection`*100,2) ELSE 0 END),'%') AS 'No ratio',
  SUM(CASE C.`drugs_result` WHEN 'Not filled in' THEN C.`Quantity` ELSE 0 END ) AS 'Not filled in',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'Not filled in' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Not filled in ratio'
FROM
(
    SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`quantity`,AA.`total number of detections`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections 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, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity 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.`Total number of detections`<>'                                        
) C
GROUP BY C.project_name,C.antibiotic_dict_name;

5. Check the results and convert successfully

This is the end of this article about the sample code of MySQL row-column conversion. For more relevant MySQL row-column conversion content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Database implementation of row and column conversion (mysql example)
Implementation of dynamic conversion of mysql rows and columns (contingency table, cross table)

<<: Detailed explanation of js's event loop event queue in the browser

>>: When is it appropriate to use dl, dt, and dd?