表结构如下:create table date(day varchar(100) not null);
create table register(registerCount bigint not null);
create table login(loginCount bigint not null);原sql如下: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
create table register(registerCount bigint not null);
create table login(loginCount bigint not null);原sql如下: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
解决方案 »
- 用mysql写一个 case when的自定义函数要怎么写
- MySql导入数据,怎么会这样意外!!!
- 建立表的问题
- 如何写.bat文件,连接PostgreSQl,执行SQL文件.
- 把mysql的data目录下的数据库文件拷贝到另一台机器的mysql的data目录下,为什么myisam的表可以用,innoDB却出错?
- VC++ ,应用mysql存储图片并可以读取图片
- 请问mysql中执行select语句后,如何把记录显示出来??
- mysql的gui管理工具winmysqladmin中database里面没有create database选项
- Excel导入Mysql中文乱码
- SQL 查询
- 高手帮忙看看mysql挂掉的原因,有日志
- 求postgreSQL 学习资源
-> 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>
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
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...