解决方案 »
- 一个简单sql ,为何查询速度超级慢?急!!
- Fatal error encountered during command execution.
- linux下mysql如何查看back_log大小
- 一条难解的sql ,请高人帮手?
- 这个sql应该怎么改
- 请高人指点:Warning: mysql_connect(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- 在线等.简单的问题
- 请教一下mysqldump的格式
- 请问jdk访问mysql是不是还要下载个connector/J ?
- 再问一下,如何安装DBI ? (安装环境:win2000,apache,perl,php,mysql,mod_perl)
- 救命!IIS7不能连接mysql,但vs里面都没问题
- 通过客户端访问服务器查询的数据库大小为什么和通过操作系统查的大小不一样?
-> select create_time, name, '' as fee from user
-> union all
-> select create_time, '' as name, fee from pay
-> ) t group by t.create_time
-> order by create_time ;
+--------------------+--------------+------------+
| max(t.create_time) | max(t. name) | max(t.fee) |
+--------------------+--------------+------------+
| 2014-11-20 | | 1.0 |
| 2014-11-21 | tom | |
| 2014-11-22 | | 2.0 |
| 2014-11-23 | jim | 2.0 |
| 2014-11-24 | lily | |
+--------------------+--------------+------------+
5 rows in set (0.01 sec)
mysql> select max(t.create_time), max(t. name), max(t.fee) from (
-> select create_time, name, '' as fee from user
-> union all
-> select create_time, '' as name, fee from pay
-> ) t group by t.create_time
-> order by create_time ;
+--------------------+--------------+------------+
| max(t.create_time) | max(t. name) | max(t.fee) |
+--------------------+--------------+------------+
| 2014-11-20 | | 1.0 |
| 2014-11-21 | tom | |
| 2014-11-22 | | 2.0 |
| 2014-11-23 | jim | 2.0 |
| 2014-11-24 | lily | |
+--------------------+--------------+------------+
5 rows in set (0.01 sec)
我说的左侧的时间不是两张表的createTime的排序,是一个新的时间,这样说吧,我是统计20号到25号的数据,这20号到25号的日期我怎么获得?不是从那两张表中获得的~
-> select create_time, name, '' as fee from user
-> union all
-> select create_time, '' as name, fee from pay ) t
-> group by t.create_time,t.name order by t.create_time;
+-------------+------+------------+
| create_time | name | sum(t.fee) |
+-------------+------+------------+
| 2014-11-20 | | 1 |
| 2014-11-21 | tom | 0 |
| 2014-11-22 | | 2 |
| 2014-11-23 | | 2 |
| 2014-11-23 | jim | 0 |
| 2014-11-24 | jim | 0 |
| 2014-11-24 | lily | 0 |
+-------------+------+------------+
7 rows in set (0.00 sec)mysql>
我说的左侧的时间不是两张表的createTime的排序,是一个新的时间,这样说吧,我是统计20号到25号的数据,这20号到25号的日期我怎么获得?不是从那两张表中获得的~
晕, 那你就用程序写吧。 或者存储过程。
我说的左侧的时间不是两张表的createTime的排序,是一个新的时间,这样说吧,我是统计20号到25号的数据,这20号到25号的日期我怎么获得?不是从那两张表中获得的~
晕, 那你就用程序写吧。 或者存储过程。我考虑到用程序写,需要循环查询好多次,这样效率好低好低吧
select daily.create_date, IFNULL(tb.name, ''), IFNULL(tb.fee, '') from (
select '2014-11-21' as create_date union all
select '2014-11-22' as create_date union all
select '2014-11-23' as create_date union all
select '2014-11-24' as create_date union all
select '2014-11-25' as create_date ) as daily
left join
(select t.create_time, t. name, sum(t.fee) as fee from (
select create_time, name, '' as fee from user
union all
select create_time, '' as name, fee from pay ) t
group by t.create_time,t.name order by t.create_time) as tb
on daily.create_date = tb.create_time
mysql> select daily.create_date, IFNULL(tb.name, ''), IFNULL(tb.fee, '') from (
-> select date_sub(curdate(), interval 5 day) as create_date union all
-> select date_sub(curdate(), interval 4 day) as create_date union all
-> select date_sub(curdate(), interval 3 day) as create_date union all
-> select date_sub(curdate(), interval 2 day) as create_date union all
-> select date_sub(curdate(), interval 1 day) as create_date ) as daily
-> left join
-> (select t.create_time, t. name, sum(t.fee) as fee from (
-> select create_time, name, '' as fee from user
-> union all
-> select create_time, '' as name, fee from pay ) t
-> group by t.create_time,t.name order by t.create_time) as tb
-> on daily.create_date = tb.create_time;
+-------------+---------------------+--------------------+
| create_date | IFNULL(tb.name, '') | IFNULL(tb.fee, '') |
+-------------+---------------------+--------------------+
| 2014-11-21 | tom | 0 |
| 2014-11-22 | | 2 |
| 2014-11-23 | | 2 |
| 2014-11-23 | jim | 0 |
| 2014-11-24 | jim | 0 |
| 2014-11-24 | lily | 0 |
| 2014-11-25 | | |
+-------------+---------------------+--------------------+
7 rows in set (0.00 sec)
狼头有更好的方法吗???