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...搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql队列转置，求sql
