mysql> select * from bytjformon;
+---------+---------------------- -+--
| daytime | yewu| dinggou | tuiding |
+---------+-----------------------+---
| 2008-12 | 704 | 376 | 255 |
| 2009-01 | 704 | 129 | 45 |
| 2009-02 | 704 | 93 | 51 |
| 2009-03 | 3006| 11 | 69 |
| 2009-04 | 704 | 4 | 61 |
| 2009-05 | 704 | 4 | 39 |
| 2009-08 | 3007| 2 | 6 |
| 2009-09 | 704 | 1 | 38 |
| 2009-06 | 3006 | 1 | 0 |
| 2009-07 | 3006 | 770 | 135 |
| 2009-08 | 3006 | 1580 | 399 |
| 2009-09 | 3006 | 610 | 327 |
| 2009-06 | 3007 | 3 | 0 |
| 2009-07 | 3007 | 11 | 3 |
| 2009-08 | 3007 | 32 | 11 |
| 2009-09 | 3007 | 18 | 16 |
+---------+-----------------------+----
18 rows in set (0.00 sec)另一个表
mysql> select * from bycmforday LIMIT 0,10;
+-----+-----+------+------------+----------+
| id | yewu| num | tjtime | timetype |
+-----+-----------------------+------+------
| 396 | 704 | 5 | 2008-12-18 | 0 |
| 397 | 704 | 8 | 2008-12-19 | 0 |
| 398 | 3006| 28 | 2008-12-20 | 0 |
| 399 | 704 | 39 | 2008-12-21 | 0 |
| 400 | 3007| 46 | 2008-12-22 | 0 |
| 401 | 3006| 51 | 2008-12-23 | 0 |
| 402 | 704 | 90 | 2008-12-24 | 0 |
| 403 | 704 | 91 | 2008-12-25 | 0 |
| 404 | 3007| 99 | 2008-12-26 | 0 |
| 405 | 704 | 110 | 2008-12-27 | 0 |
+-----+-----------------------+------+------
10 rows in set (0.00 sec)
查询第一张表的数据的时候附带第二张表的num列
table1是每月统计一次的数据,table2是每天统计的
查询table1的时候附带table2该月相同业务最后一天的num
+---------+---------------------- -+--
| daytime | yewu| dinggou | tuiding |num |
+---------+-----------------------+---
| 2008-12 | 704 | 376 | 255 | 110|
| 2008-12 | 3006| 376 | 255 | 54 |
| 2008-12 | 3007| 376 | 255 | 99 |
| 2009-02 | 704 | 93 | 51 | |
| 2009-03 | 704 | 11 | 69 | |
| 2009-04 | 704 | 4 | 61 | |+---------+-----------------------+----
+---------+---------------------- -+--
| daytime | yewu| dinggou | tuiding |
+---------+-----------------------+---
| 2008-12 | 704 | 376 | 255 |
| 2009-01 | 704 | 129 | 45 |
| 2009-02 | 704 | 93 | 51 |
| 2009-03 | 3006| 11 | 69 |
| 2009-04 | 704 | 4 | 61 |
| 2009-05 | 704 | 4 | 39 |
| 2009-08 | 3007| 2 | 6 |
| 2009-09 | 704 | 1 | 38 |
| 2009-06 | 3006 | 1 | 0 |
| 2009-07 | 3006 | 770 | 135 |
| 2009-08 | 3006 | 1580 | 399 |
| 2009-09 | 3006 | 610 | 327 |
| 2009-06 | 3007 | 3 | 0 |
| 2009-07 | 3007 | 11 | 3 |
| 2009-08 | 3007 | 32 | 11 |
| 2009-09 | 3007 | 18 | 16 |
+---------+-----------------------+----
18 rows in set (0.00 sec)另一个表
mysql> select * from bycmforday LIMIT 0,10;
+-----+-----+------+------------+----------+
| id | yewu| num | tjtime | timetype |
+-----+-----------------------+------+------
| 396 | 704 | 5 | 2008-12-18 | 0 |
| 397 | 704 | 8 | 2008-12-19 | 0 |
| 398 | 3006| 28 | 2008-12-20 | 0 |
| 399 | 704 | 39 | 2008-12-21 | 0 |
| 400 | 3007| 46 | 2008-12-22 | 0 |
| 401 | 3006| 51 | 2008-12-23 | 0 |
| 402 | 704 | 90 | 2008-12-24 | 0 |
| 403 | 704 | 91 | 2008-12-25 | 0 |
| 404 | 3007| 99 | 2008-12-26 | 0 |
| 405 | 704 | 110 | 2008-12-27 | 0 |
+-----+-----------------------+------+------
10 rows in set (0.00 sec)
查询第一张表的数据的时候附带第二张表的num列
table1是每月统计一次的数据,table2是每天统计的
查询table1的时候附带table2该月相同业务最后一天的num
+---------+---------------------- -+--
| daytime | yewu| dinggou | tuiding |num |
+---------+-----------------------+---
| 2008-12 | 704 | 376 | 255 | 110|
| 2008-12 | 3006| 376 | 255 | 54 |
| 2008-12 | 3007| 376 | 255 | 99 |
| 2009-02 | 704 | 93 | 51 | |
| 2009-03 | 704 | 11 | 69 | |
| 2009-04 | 704 | 4 | 61 | |+---------+-----------------------+----
解决方案 »
- 高手给看看这是什么原因啊
- acmain_chm,mysql达人们 进
- 简单 --- mysql 字符问题
- 求远程访问mysql速度慢的解决办法?
- Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (gb2312_chinese_ci,错误
- 求个更好的解决办法~~~?
- 一个比较棘手的问题,望高手解答
- Mysql数据库中怎样快速查询符合条件的记录是否存在
- 关于MySQL通配符的问题
- mysql字符串拼接,拼接的结果过长被截取了。。。
- 2台web服务器朝数据库服务器的同一个表插入数据 发生重复id错误
- 我MY SQL 数据库链接应该怎么写?
(select b.num from bycmforday b where b.yewu=a.yewu and date_format(b.tjtime,'%Y-%m')=a.daytime and not exists (select 1 from bycmforday c where c.yewu=b.yewu and c.daytime>b.daytime)) as num
from bytjformon a;
b2 a on c.yewu=a.yewu
inner join
(select a.yewu,max(a.tjtime) as ma from b2 )b on a.yewu=b.yewu and a.tjtime=b.ma
select a.*,
(select b.num from bycmforday b where b.yewu=a.yewu and date_format(b.tjtime,'%Y-%m')=a.daytime and not exists (select 1 from bycmforday c where c.yewu=b.yewu and c.tjtime>b.tjtime)) as num
from bytjformon a;
+---------+------+---------+---------+
| daytime | yewu | dinggou | tuiding |
+---------+------+---------+---------+
| 2008-12 | 704 | 376 | 255 |
| 2009-01 | 704 | 129 | 45 |
| 2009-02 | 704 | 93 | 51 |
| 2009-03 | 3006 | 11 | 69 |
| 2009-04 | 704 | 4 | 61 |
| 2009-05 | 704 | 4 | 39 |
| 2009-08 | 3007 | 2 | 6 |
| 2009-09 | 704 | 1 | 38 |
| 2009-06 | 3006 | 1 | 0 |
| 2009-07 | 3006 | 770 | 135 |
| 2009-08 | 3006 | 1580 | 399 |
| 2009-09 | 3006 | 610 | 327 |
| 2009-06 | 3007 | 3 | 0 |
| 2009-07 | 3007 | 11 | 3 |
| 2009-08 | 3007 | 32 | 11 |
| 2009-09 | 3007 | 18 | 16 |
+---------+------+---------+---------+
16 rows in set (0.00 sec)mysql> select * from bycmforday;
+------+------+------+------------+----------+
| id | yewu | num | tjtime | timetype |
+------+------+------+------------+----------+
| 396 | 704 | 5 | 2008-12-18 | 0 |
| 397 | 704 | 8 | 2008-12-19 | 0 |
| 398 | 3006 | 28 | 2008-12-20 | 0 |
| 399 | 704 | 39 | 2008-12-21 | 0 |
| 400 | 3007 | 46 | 2008-12-22 | 0 |
| 401 | 3006 | 51 | 2008-12-23 | 0 |
| 402 | 704 | 90 | 2008-12-24 | 0 |
| 403 | 704 | 91 | 2008-12-25 | 0 |
| 404 | 3007 | 99 | 2008-12-26 | 0 |
| 405 | 704 | 110 | 2008-12-27 | 0 |
+------+------+------+------------+----------+
10 rows in set (0.00 sec)mysql> select a.*,
-> (select b.num from bycmforday b where b.yewu=a.yewu and date_format(b.tjt
ime,'%Y-%m')=a.daytime and not exists (select 1 from bycmforday c where c.yewu=b
.yewu and c.tjtime>b.tjtime)) as num
-> from bytjformon a;
+---------+------+---------+---------+------+
| daytime | yewu | dinggou | tuiding | num |
+---------+------+---------+---------+------+
| 2008-12 | 704 | 376 | 255 | 110 |
| 2009-01 | 704 | 129 | 45 | NULL |
| 2009-02 | 704 | 93 | 51 | NULL |
| 2009-03 | 3006 | 11 | 69 | NULL |
| 2009-04 | 704 | 4 | 61 | NULL |
| 2009-05 | 704 | 4 | 39 | NULL |
| 2009-08 | 3007 | 2 | 6 | NULL |
| 2009-09 | 704 | 1 | 38 | NULL |
| 2009-06 | 3006 | 1 | 0 | NULL |
| 2009-07 | 3006 | 770 | 135 | NULL |
| 2009-08 | 3006 | 1580 | 399 | NULL |
| 2009-09 | 3006 | 610 | 327 | NULL |
| 2009-06 | 3007 | 3 | 0 | NULL |
| 2009-07 | 3007 | 11 | 3 | NULL |
| 2009-08 | 3007 | 32 | 11 | NULL |
| 2009-09 | 3007 | 18 | 16 | NULL |
+---------+------+---------+---------+------+
16 rows in set (0.00 sec)mysql>
bycmforday a on c.yewu=a.yewu and c.daytime=date_format(a.tjtime,'%Y-%m')
inner join
(select a.yewu,max(a.tjtime) as ma from bycmforday )b on a.yewu=b.yewu and a.tjtime=b.ma
left join bycmforday b
on b.yewu=a.yewu and date_format(b.tjtime,'%Y-%m')=a.daytime
where not exists (select 1 from bycmforday c where c.yewu=b.yewu and c.tjtime>b.tjtime)
-> left join bycmforday b
-> on b.yewu=a.yewu and date_format(b.tjtime,'%Y-%m')=a.daytime
-> where not exists (select 1 from bycmforday c where c.yewu=b.yewu and c.tj
time>b.tjtime);
+---------+------+---------+---------+------+
| daytime | yewu | dinggou | tuiding | num |
+---------+------+---------+---------+------+
| 2008-12 | 704 | 376 | 255 | 110 |
| 2009-01 | 704 | 129 | 45 | NULL |
| 2009-02 | 704 | 93 | 51 | NULL |
| 2009-03 | 3006 | 11 | 69 | NULL |
| 2009-04 | 704 | 4 | 61 | NULL |
| 2009-05 | 704 | 4 | 39 | NULL |
| 2009-08 | 3007 | 2 | 6 | NULL |
| 2009-09 | 704 | 1 | 38 | NULL |
| 2009-06 | 3006 | 1 | 0 | NULL |
| 2009-07 | 3006 | 770 | 135 | NULL |
| 2009-08 | 3006 | 1580 | 399 | NULL |
| 2009-09 | 3006 | 610 | 327 | NULL |
| 2009-06 | 3007 | 3 | 0 | NULL |
| 2009-07 | 3007 | 11 | 3 | NULL |
| 2009-08 | 3007 | 32 | 11 | NULL |
| 2009-09 | 3007 | 18 | 16 | NULL |
+---------+------+---------+---------+------+
16 rows in set (0.00 sec)mysql>
select tm.*,bd.num
from bytjformon tm left join bycmforday bd
on tm.yewu=bd.yewu and year(tm.daytime)=year(bd.tjtime) and
month(tm.daytime)=month(bd.tjtime)
where not exists
(select 1 from bycmforday where yewu=tm.yewu and year(bjtime)=year(bd.bjtime)
and month(bjtime)=month(bd.bjtime) and bjtime>bd.bjtime);
select num
from bycmforday b
where yewu=a.yewu
and date_formt(tjtime,'%Y-%m')=a.daytime
and not exists (select 1 from bycmforday where yewu=a.yewu and date_formt(tjtime,'%Y-%m')=a.daytime and tjtime>b.tjtime)
) as num
from bytjformon a