mysql队列转置，求sql

2012-09-10

mysql行列转置，求sql表结构如下：SQL codecreate table date(day varchar(100) not null)create table reg

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,loginCountfrom(    select day from date where day between 20120101 and 20120103)tmpleft join(    select registerCount from register)r on tmp.day=r.dayleft join(    select loginCount from login)l on tmp.day=l.daygroup 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...

热点排行

Mysql

mysql队列转置，求sql