MySQL GROUP BY列并在不同的列中显示值 [英] MySQL GROUP BY a column and show values in different columns

问题描述

我有一张下表.对于ID2的每个不同值和时间"，需要选择(0,1,2,3,4)中具有ID3的行.然后，我想在5列中显示"Val"，并用零填充空白列. (如果ID3 = 0，则col0 = Val 如果ID3 = 1，则col1 = Val，然后...)

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

换句话说，输出应如下所示(按ID2分组，时间):

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 

注意:表有5000万行.

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

这篇关于MySQL GROUP BY列并在不同的列中显示值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！