mysql中group by展示无值,MySQL GROUP BY列并在不同列中显示值

I have a table as below. It is required for each distinct value of ID2, and Time to select rows that have ID3 in (0,1,2,3,4). Then, I want to show 'Val' in 5 columns, and fill the empty columns with zero.

(If ID3=0 then col0=Val

If ID3=1 then col1=Val, and ...)

ID1 ID2 ID3 Val Time

1 224 0 224 10

2 224 1 48 10

3 224 4 270 15

4 224 5 1000 27

5 225 0 231 10

6 225 1 400 10

7 225 0 100 20

In other words, the output should be as follow (group by ID2, Time ):

ID2 Time col0 col1 col2 col3 col4

224 10 224 48 0 0 0

224 15 0 0 0 0 270

225 10 231 400 0 0 0

225 20 100 0 0 0 0

Note: table has 50M rows.

解决方案select id2, time,

sum(case when id3 = 0 then val else 0 end) as col0,

sum(case when id3 = 1 then val else 0 end) as col1,

sum(case when id3 = 2 then val else 0 end) as col2,

sum(case when id3 = 3 then val else 0 end) as col3,

sum(case when id3 = 4 then val else 0 end) as col4

from your_table

group by id2, time

having sum(val) > 0