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);  

欢迎大家阅读《mysql队列转置,求sql》,跪求各位点评,by 搞代码

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

原创文章,转载请注明: 转载自搞代码

本文链接地址: mysql队列转置,求sql

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注