mysql行列转置，求sql

Question

表结构如下：

 

create table date(day varchar(100) not null);
create table register(registerCount bigint not null);
create table login(loginCount bigint not null);

 原sql如下：select day,registerCount,loginCount

from(
    select day from date where day between 20120101 and 20120103)tmp
left join(
    select registerCount from register)r on tmp.day=r.day
left join(
    select loginCount from login)l on tmp.day=l.day
group by day;

  原结果集如下：

想更改为如下结果：

水平有限，请高手搭救，要详细sql

 

Answer 1

你这条sql 貌似还有问题吧 ，先参考这个 看看
[code="java"]表名：table
字段：user , id , score
数据如下：
+----------+----------+----------------+
| user | id | score |
| u1 | 1 | 1.2 |
| u1 | 2 | 2.3 |
| u1 | 3 | 2.5 |
| u2 | 1 | 3.4 |
| u2 | 2 | 4.5 |
| u2 | 3 | 2.6 |
+----------+----------+----------------+
需要得到如下的查询结果：
+----------+----------------------+----------------------+----------------------+
| user | id_1_score | id_2_score | id_3_score |
| u1 | 1.2 | 2.3 | 2.5 |
| u2 | 3.4 | 4.5 | 2.6 |
+----------+----------------------+----------------------+----------------------+

SQL语句：
Select user,
sum(if(id=1, score,0)) as id_1_score,
sum(if(id=2, score,0)) as id_2_score,
sum(if(id=3, score,0)) as id_3_score
from table
group by user;[/code]