mysql行列转置，求sql

[shanbaooo]

表结构如下：

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

[nicenight]

mysql> select
    ->     type,
    ->     sum(if(tmp.day = 20120101, registerCount, 0)) as '20120101',
    ->     sum(if(tmp.day = 20120102, registerCount, 0)) as '20120102',
    ->     sum(if(tmp.day = 20120103, registerCount, 0)) as '20120103'
    -> from
    ->     (
    ->     select day
    ->     from date
    ->     where day between 20120101 and 20120103
    ->     )tmp
    ->     left join
    ->     (
    ->     select
    ->     day,
    ->     "registerCount" as type,
    ->     registerCount
    ->     from register
    ->     ) r
    ->     on tmp.day=r.day
    ->     group by type
    ->
    ->     union
    -> select
    ->     type,
    ->     sum(if(tmp.day = 20120101, loginCount, 0)) as '20120101',
    ->     sum(if(tmp.day = 20120102, loginCount, 0)) as '20120102',
    ->     sum(if(tmp.day = 20120103, loginCount, 0)) as '20120103'
    -> from
    ->     (
    ->     select day
    ->     from date
    ->     where day between 20120101 and 20120103
    ->     )tmp
    ->     left join
    ->     (
    ->     select
    ->     day,
    ->     "loginCount" as type,
    ->     loginCount
    ->     from login
    ->     )l
    ->     on tmp.day=l.day
    ->     group by type;
+---------------+----------+----------+----------+
| type          | 20120101 | 20120102 | 20120103 |
+---------------+----------+----------+----------+
| registerCount |        1 |        2 |        3 |
| loginCount    |        4 |        5 |        6 |
+---------------+----------+----------+----------+
2 rows in set (0.00 sec)

mysql>

[wangjianbo88]

4楼不错

[wwwwb]

在EXCEL中最简单
SQL语句：
你的SQL存为VIEW1
SELECT Sum(if(day = 20120101, registerCount, 0)) as '20120101',
sum(if(day = 20120102, registerCount, 0)) as '20120102',
sum(if(day = 20120103, registerCount, 0)) as '20120103'
FROM (SELECT registerCount FROM VIEW1) A
UNION ALL
SELECT Sum(if(day = 20120101, loginCount, 0)) as '20120101',
sum(if(day = 20120102, loginCount, 0)) as '20120102',
sum(if(day = 20120103, loginCount, 0)) as '20120103'
FROM (SELECT loginCount FROM VIEW1) A

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943
MySQL交叉表
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[shanbaooo]

非常感谢诸位大佬！！！

[rucypli]

这个最简单方法就是放excel里面进行行列转换

[shanbaooo]

是啊，可是客户提了需求，老大下了命令，我们做小弟的只能一边心里骂娘一边顶着上了啊……老大支个招吧，小弟技穷了[Quote=引用 1 楼 的回复:]

这个最简单方法就是放excel里面进行行列转换
[/Quote]

[rucypli]

这个百度吧 很多例子

http://www.2cto.com/database/201203/122386.html