mysql row column conversion sample code

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?

Recommend

Detailed explanation of Getter usage in vuex

Preface Vuex allows us to define "getters&qu...

Angular performance optimization: third-party components and lazy loading technology

Table of contents Overview Environment Preparatio...

nginx automatically generates configuration files in docker container

When a company builds Docker automated deployment...

Detailed explanation of the use of docker tag and docker push

Docker tag detailed explanation The use of the do...

How to hide the version number in Nginx

Nginx hides version number In a production enviro...

Centos7 install mysql5.6.29 shell script

This article shares the shell script of mysql5.6....

Solution to MySQL being unable to start due to excessive memory configuration

Problem Description MySQL reports an error when s...

Example of how to optimize MySQL insert performance

MySQL Performance Optimization MySQL performance ...

How InnoDB cleverly implements transaction isolation levels

Preface In the previous article Detailed Explanat...

A brief discussion on the issue of dates containing zero values ​​in MySQL database

By default, MySQL can accept the insertion of 0 v...

vue3 timestamp conversion (without using filters)

When vue2 converts timestamps, it generally uses ...

How to install mysql5.6 in docker under ubuntu

1. Install mysql5.6 docker run mysql:5.6 Wait unt...

IE8 provides a good experience: Activities

Today I had a sneak peek at IE8 beta 1 (hereafter...

Detailed analysis of each stage of nginx's http request processing

When writing the HTTP module of nginx, it is nece...

Vue2.x responsiveness simple explanation and examples

1. Review Vue responsive usage​ Vue responsivenes...