普通にピボット処理でしょうね
SQL
1create table tbl(id int primary key auto_increment,user varchar(10),year int,val int,unique key(user,year));
2
3insert into tbl(user,year,val) values
4('AAA',2000,3),
5('BBB',2000,54),
6('CCC',2000,5),
7('AAA',2001,7),
8('BBB',2001,4),
9('CCC',2001,86),
10('AAA',2002,65),
11('BBB',2002,42),
12('CCC',2002,31),
13('AAA',2003,56),
14('BBB',2003,4),
15('CCC',2003,21);
これを命題の形式にしているのはこう
PHP
1select user
2,case year when 2000 then 1 else 0 end * val as x2000
3,case year when 2001 then 1 else 0 end * val as x2001
4,case year when 2002 then 1 else 0 end * val as x2002
5,case year when 2003 then 1 else 0 end * val as x2003
6from tbl
希望しているのは単に集計
PHP
1select user
2,sum(case year when 2000 then 1 else 0 end * val) as x2000
3,sum(case year when 2001 then 1 else 0 end * val) as x2001
4,sum(case year when 2002 then 1 else 0 end * val) as x2002
5,sum(case year when 2003 then 1 else 0 end * val) as x2003
6from tbl
7group by user
蛇足
これをmysqlでやるときprocedureでこう作っておきます
SQL
1drop procedure if exists shukei;
2delimiter //
3create procedure shukei(IN start_year int,IN end_year int)
4begin
5declare year int default 0;
6set year=start_year;
7set @sql='select user';
8while year<=end_year do
9set @sql=concat(@sql,',sum((year=',year,')*val) as x',year);
10set year=year+1;
11end while;
12set @sql=concat(@sql,' from tbl group by user');
13prepare stmt from @sql;
14execute stmt;
15end
16//
17delimiter ;
集計の呼び出し
SQL
1call shukei(2000,2003);
結果:
user | x2000 | x2001 | x2002 | x2003 |
---|
AAA | 3 | 7 | 65 | 56 |
BBB | 54 | 4 | 42 | 4 |
CCC | 5 | 86 | 31 | 21 |