解决方案 »
- mysql主键问题
- 请教mysql如何在游标循环中再加一个游标循环
- 关于mysql主从复制的潜在问题,很有意思
- sql语句效率低下,高手帮忙看看
- 在线等:MySql安装之后,没有设置root用户,现在怎么进入MySQL
- 如何修改主键的值?急!!!
- heartbeat这个软件有用过的人吗?
- 非常棘手的mysql问题 大家来看看
- mysql数据库物理文件夹删除,再建立同样的数据库,显示库中的某个表已经存在而建立失败
- 选取记录中姓名相同的记录 并将除了第一条之外的姓名加上星号
- 救命!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)
狼头有更好的方法吗???