mysql sql语句队列_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...

原创文章，转载请注明： 转载自搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~