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

Solve the problem that docker installation is completed and reported: bridge-nf-call-iptables is disabled

After the docker installation is completed on the...

Vue ElementUI implements asynchronous loading tree

This article example shares the specific code of ...

A simple method to implement Linux timed log deletion

Introduction Linux is a system that can automatic...

Detailed explanation of the principle of Vue monitoring data

Table of contents 1. Introduction II. Monitoring ...

Quickly solve the Chinese input method problem under Linux

Background: I'm working on asset reporting re...

How to get the intersection/difference/union of two sets in mysql

Common scenarios of MySQL: getting the intersecti...

Sample code for configuring nginx to support https

1. Introduction Are you still leaving your websit...

React mouse multi-selection function configuration method

Generally, lists have selection functions, and si...

Solution to the problem that MySQL can be started when installed in WAMP but cannot be started after restart

After installing wamp for the first time, all ser...

mysql obtains statistical data within a specified time period

mysql obtains statistical data within a specified...

Analysis and description of network configuration files under Ubuntu system

I encountered a strange network problem today. I ...

A brief discussion on the lazy loading attribute pattern in JavaScript

Table of contents 1. Introduction 2. On-demand at...

Example code for implementing timeline and animation effects using JavaScript (front-end componentization)

Table of contents Code cleaning "Frames"...

MySQL 8.0.11 installation and configuration method graphic tutorial

The installation and configuration methods of MyS...

Tutorial on installing mysql5.7.23 on Ubuntu 18.04

This article shares with you the specific method ...