MySQL row column conversion example code

Time:2021-11-28

1、 Demand

We have three tables. We need to classify and count the different drug sensitivity results of antibiotics over a period of time, that is, report_ item_ Drugs in the drugs table_ Result in different projects_ Name and antibiotics_ dict_ Proportion under name,The drug sensitivity results are displayed on the line, the effect is as follows:

Three original tables (only take the required field examples) are:

Report form

Project table

Antibiotics table (drugs_result is a column of values)

2、 Realize

1. The total number of detected antibiotics was calculated according to the grouping of items and antibiotics

SELECT 
 A.project_ name,A.antibiotic_ dict_ Name, sum (Num) total number of as checks out
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. The number of different drug sensitivity results was calculated according to the items, antibiotics 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 detected quantity`
    FROM 
        (
              SELECT 
                A.project_ name,A.antibiotic_ dict_ Name, sum (Num) total number of as checks out
              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 detected ` < > ''

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

However, what we need is to display the drug sensitivity on the row. Direct comparison does not meet the requirements, so we need to convert the column to row

We use case when to realize row column conversion, and convert the drug sensitivity results into Chinese characters for easy reading according to the dictionary

SELECT
  C.project_ Name project name, c.antibiotic_ dict_ Name: antibiotic name, C. ` total detected quantity ',
  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 detected ` * 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 detected ` * 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 detected ` * 100,2) else 0 end), '%') as' sensitivity ratio ',
  Sum (case C. ` drugs_result ` when 'I' then C. ` quantity ` else 0 end) as' intermediary ',
  Concat (sum (case C. ` drugs_result ` when 'I' then format (C. ` quantity ` / C. ` total detected ` * 100,2) else 0 end), '%') as' intermediary ratio ',
  Sum (case C. ` drugs_result ` when 'N1' then C. ` quantity ` else 0 end) as' insensitive ',
  Concat (sum (case C. ` drugs_result ` when 'N1' then format (C. ` quantity ` / C. ` total detected ` * 100,2) else 0 end), '%') as' insensitive 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 detected ` * 100,2) else 0 end), '%') as' incomparable rate ',
  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' the format (C. ` quantity ` / C. ` total detected ` * 100,2) else 0 end), '%') as' not filled in rate '
FROM
(
    SELECT 
      BB.project_ name,BB.antibiotic_ dict_ name,BB.drugs_ Result, BB. ` quantity ', AA. ` total detected quantity`
    FROM 
        (
              SELECT 
                A.project_ name,A.antibiotic_ dict_ Name, sum (Num) total number of as checks out
              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 detected ` < > ''                                        
) C
GROUP BY C.project_name,C.antibiotic_dict_name;

5. View the results and convert successfully

This is the end of this article about the sample code of MySQL row column conversion. For more information about MySQL row column conversion, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

Android master notes – start optimization

The only way to start and open the app, the first experience, is related to core data such as user retention and conversion rate; Start analysis startup type Android vitals can monitor the application cold, hot and warm startup time. Via ADB shell am start – w Execute the command to start and print the […]