Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the method of column-column conversion in mysql". In daily operation, I believe that many people have doubts about the method of column-column conversion in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "column-column conversion methods in mysql". Next, please follow the editor to study!

The solution is as follows:

Data samples:

Create table tx (

Id int primary key

C1 char (2)

C2 char (2)

C3 int

);

Insert into tx values

(1,'A1','A1','B1','9)

(2,'A2','A1','B1')

(3,'A3','A3','B1','4)

(4,'A4','A4','B1')

(5,'A1','A1','B2')

(6,'A _ 2, B _ 2, and B _ 2)

(7,'A3,'B2,'8)

(8,'A4, B2, 5)

(9,'A1','A1','B3')

(10, 'A2century, B3century, 8)

(11, 'A3pm, B3pm, 8)

(12,'A4,'B3,'6)

(13,'A1','A1 'and B4)

(14,'A2,'A2,'B4,')

(15,'A3','A3'B4')

(16, 'A4pm / B4pm / 9)

(17,'A1','A1', B4)

(18,'A _ 2','A _ 2')

(19,'A3','A3'B4')

(20, 'A4pm, B4pm 5)

Mysql > select * from tx

+-- +

| | id | C1 | c2 | c3 |

+-- +

| | 1 | A1 | B1 | 9 |

| | 2 | A2 | B1 | 7 |

| | 3 | A3 | B1 | 4 | |

| | 4 | A4 | B1 | 2 | |

| | 5 | A1 | B2 | 2 | |

| | 6 | A2 | B2 | 9 |

| | 7 | A3 | B2 | 8 | |

| | 8 | A4 | B2 | 5 | |

| | 9 | A1 | B3 | 1 | |

| | 10 | A2 | B3 | 8 | |

| | 11 | A3 | B3 | 8 | |

| | 12 | A4 | B3 | 6 | |

| | 13 | A1 | B4 | 8 |

| | 14 | A2 | B4 | 2 |

| | 15 | A3 | B4 | 6 | |

| | 16 | A4 | B4 | 9 | |

| | 17 | A1 | B4 | 3 |

| | 18 | A2 | B4 | 5 | |

| | 19 | A3 | B4 | 2 | |

| | 20 | A4 | B4 | 5 | |

+-- +

20 rows in set (0.00 sec)

Mysql >

Expected result

+-+ +

| | C1 | B1 | B2 | B3 | B4 | Total |

+-+ +

| | A1 | 9 | 2 | 1 | 11 | 23 | |

| | A2 | 7 | 9 | 8 | 7 | 31 |

| | A3 | 4 | 8 | 8 | 8 | 28 |

| | A4 | 2 | 5 | 6 | 14 | 27 |

| | Total | 22 | 24 | 23 | 40 | 109 | |

+-+ +

1. Use SUM (IF ()) to generate columns + WITH ROLLUP to generate summary rows, and use IFNULL to display summary row headings as Total

Mysql > SELECT

-> IFNULL (C1 recording total') AS total

-> SUM (IF (c2xxxxxxxxxxxxxxxxxxxxxxxxxxxx)) AS B1

-> SUM (IF (c2cm, B2pm, c3pm 0)) AS B2

-> SUM (IF (c2pm, c3pm, c3pm 0)) AS B3

-> SUM (IF (c2cm, c2x, B4pm, c3pm 0)) AS B4

-> SUM (IF (c2pm, c3pyr0)) AS total

-> FROM (

-> SELECT C1 AS (c2) AS c2 (c3) AS c3

-> FROM tx

-> GROUP BY C1 and c2

-> WITH ROLLUP

-> HAVING C1 IS NOT NULL

->) AS A

-> GROUP BY C1

-> WITH ROLLUP

+-+ +

| | total | B1 | B2 | B3 | B4 | total | |

+-+ +

| | A1 | 9 | 2 | 1 | 11 | 23 | |

| | A2 | 7 | 9 | 8 | 7 | 31 |

| | A3 | 4 | 8 | 8 | 8 | 28 |

| | A4 | 2 | 5 | 6 | 14 | 27 |

| | total | 22 | 24 | 23 | 40 | 109 | |

+-+ +

5 rows in set, 1 warning (0.00 sec)

two。 Use SUM (IF ()) to generate columns + UNION to generate summary rows, and use IFNULL to display summary row headings as Total

Mysql > select C1

-> sum (if (c2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)) AS B1

-> sum (if (c2x, c2m, c2m, c3m 0) AS B2

-> sum (if (c2x, c2x, B3m, c3m. 0) AS B3

-> sum (if (c2pm B4pm C3Magi 0)) AS B4 AS TOTAL sum (C3) AS TOTAL

-> from tx

-> group by C1

-> UNION

-> SELECT 'TOTAL',sum (if (c2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)) AS B1

-> sum (if (c2x, c2m, c2m, c3m 0) AS B2

-> sum (if (c2x, c2x, B3m, c3m. 0) AS B3

-> sum (if (c2pm B4pm C3Magi 0)) AS B4 FROM TX sum (C3) FROM TX

->

+-+ +

| | C1 | B1 | B2 | B3 | B4 | TOTAL |

+-+ +

| | A1 | 9 | 2 | 1 | 11 | 23 | |

| | A2 | 7 | 9 | 8 | 7 | 31 |

| | A3 | 4 | 8 | 8 | 8 | 28 |

| | A4 | 2 | 5 | 6 | 14 | 27 |

| | TOTAL | 22 | 24 | 23 | 40 | 109 | |

+-+ +

5 rows in set (0.00 sec)

Mysql >

3. Use SUM (IF ()) to generate columns and generate results directly instead of using subqueries

Mysql > select ifnull (C1 recording total')

-> sum (if (c2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)) AS B1

-> sum (if (c2x, c2m, c2m, c3m 0) AS B2

-> sum (if (c2x, c2x, B3m, c3m. 0) AS B3

-> sum (if (c2pm B4pm C3Magi 0)) AS B4 AS TOTAL sum (C3) AS TOTAL

-> from tx

-> group by C1 with rollup

+-+ +

| | ifnull (C1) | B1 | B2 | B3 | B4 | TOTAL |

+-+ +

| | A1 | 9 | 2 | 1 | 11 | 23 | |

| | A2 | 7 | 9 | 8 | 7 | 31 |

| | A3 | 4 | 8 | 8 | 8 | 28 |

| | A4 | 2 | 5 | 6 | 14 | 27 |

| | total | 22 | 24 | 23 | 40 | 109 | |

+-+ +

5 rows in set (0.00 sec)

Mysql >

4. Dynamic, suitable for column uncertainty

Mysql > SET @ EE=''

Mysql > SELECT @ EE:=CONCAT (@ EE,'SUM (IF (C _ 2 =\', C _ 2 FROM TX', C _ 2 FROM TX)) AS', C _ 2 FROM TX) FROM (C _ 2 _ 2 FROM TX) A

Mysql > SET @ QQ=CONCAT ('SELECT ifnull (C1,\' total\'),', LEFT (@ EE,LENGTH (@ EE)-1),', SUM (C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP')

Query OK, 0 rows affected (0.00 sec)

Mysql > PREPARE stmt2 FROM @ QQ

Query OK, 0 rows affected (0.00 sec)

Statement prepared

Mysql > EXECUTE stmt2

+-+ +

| | ifnull (C1) | B1 | B2 | B3 | B4 | TOTAL |

+-+ +

| | A1 | 9 | 2 | 1 | 11 | 23 | |

| | A2 | 7 | 9 | 8 | 7 | 31 |

| | A3 | 4 | 8 | 8 | 8 | 28 |

| | A4 | 2 | 5 | 6 | 14 | 27 |

| | total | 22 | 24 | 23 | 40 | 109 | |

+-+ +

5 rows in set (0.00 sec)

Mysql >

The above are all provided by netizens liangCK, wwwwb, WWWWA and dap570. Thank you again for their support.

In fact, CASE WHEN / DECODE can also be used instead of IF in the database.

At this point, the study of "column-column conversion method in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.