mysql队列转置，求sql

mysql行列转置，求sql

表结构如下：

SQL code


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

原sql如下：

SQL code


  
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

SQL code

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>



在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


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...

相关文章：

你感兴趣的文章：

标签云：