How to deal with such result?

CodePudding user response:

Mobile phone is bad code code, hope to adopt

CodePudding user response:

Mysql> Select * from score;
+ - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + +
-- -- -- -- --| | id name during | | the month | score |
+ - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + +
-- -- -- -- --| | 1 zhang SAN 36 | | 2017 | 2 |
| | 2 threes | 2017 | 3 | 98 |
| 3 | threes 22 | | 2017 | | 4
| | 4 zhang SAN 40 | | 2018 | | 1
| | 5 zhang SAN 60 | | 2018 | 2 |
| | 6 zhang 50 | | 2018 | 3 |
| | 7 threes | 2018 | | 80 |
8 | | zhang 25 | | 2017 | | 1
+ - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + +
-- -- -- -- --

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Mysql> Select the name,
-> Year,
-> Max (case the month when '1' then score else 0 end) '1',
-> Max (case the month when '2' end then score else 0) '2',
-> Max (case the month when the '3' end then score else 0) '3',
-> Max (case the month when '4' end then score else 0) '4'
-> The from score
-> Group by name, year,
-> The order by year desc;
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - +
During | name | | 1 | 2 | 3 | 4 |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - +
| * * | | | | 60 40 to 50 2018 | 80 |
| zhang SAN | 2017 | | 98 | | 36 25 22 |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - +

CodePudding user response:

Baidu a line list of many

CodePudding user response:

Baidu WM_CONCAT function on its own

CodePudding user response:

The following SQL can fulfill your requirements:

 
The create table TMP as 
Select '2017' c1, c2, 1 3100 c3 from dual union all 
Select '2017' c1 and c2, 2 3200 c3 from dual union all 
Select '2017' c1 and c2, 3 3300 c3 from dual union all 
Select '2017' c1 and c2, 4 3400 c3 from dual union all 
Select '2018' c1, c2, 1 4100 c3 from dual union all 
Select '2018' c1 and c2, 2 4200 c3 from dual union all 
Select '2018' c1 and c2, 3 4300 c3 from dual union all 
Select '2018' c1 and c2, 4 4400 c3 from dual; 

The select c1 as "year", 
Max (decode (c2, c3, null)) as "in January," 
Max (decode (c2, 2, c3, null)) as "in February," 
Max (decode (c2, 3, c3, null)) as "march", 
Max (decode (c2, 4, c3, null)) as "April" 
The from TMP 
Group by c1