有两个表tb1,字段id,有3条记录:
1
2
3
表tb2,字段num,tb1_id,record_date,有如下记录:
10, 1, '2011-08-15'
11, 2, '2011-08-15'
12, 1, '2011-08-16'
13, 2, '2011-08-16'
14, 3, '2011-08-16'
15, 2, '2011-08-17'
想得到这样的结果:
1, 10, '2011-08-15'
2, 11, '2011-08-15'
3, 0, '2011-08-15'
1, 12, '2011-08-16'
2, 13, '2011-08-16'
3, 14, '2011-08-16'
1, 0, '2011-08-17'
2, 15, '2011-08-17'
3, 0, '2011-08-17'请问怎么写sql语句?
1
2
3
表tb2,字段num,tb1_id,record_date,有如下记录:
10, 1, '2011-08-15'
11, 2, '2011-08-15'
12, 1, '2011-08-16'
13, 2, '2011-08-16'
14, 3, '2011-08-16'
15, 2, '2011-08-17'
想得到这样的结果:
1, 10, '2011-08-15'
2, 11, '2011-08-15'
3, 0, '2011-08-15'
1, 12, '2011-08-16'
2, 13, '2011-08-16'
3, 14, '2011-08-16'
1, 0, '2011-08-17'
2, 15, '2011-08-17'
3, 0, '2011-08-17'请问怎么写sql语句?
解决方案 »
- 如何用mysqldump导出忽略主键的表数据?
- 请教安装mysql cluster的问题
- sql语句求助
- MySQL创建表出错了,列类型可能定义的不对,大家帮忙看看
- 请高手们帮忙看这样一条语句。
- 取得一个表中的某一列的排名
- 如何在MySQL里面实现互斥的访问?如何保证select/update的原子操作?使用lock tables和unlock tables的效果如何?
- 怎么样从mysql表中找出所有某个字段值相同的记录
- 要命的问题, 第一步连接服务器都连接不了, help!
- MySQL 一张表合并查询
- 请问,存储过程里的IF字符串比较只能用strcmp吗?直接用'a'='a'会报错啊……
- 【nicenight】还在吗?接着上个问题
select tb1_id,num,record_date from tb1 group by record_date order by tb1_id;
试下
2011-08-16这天的记录tb1_id是都有的
create temporary table tempRecordDate(
record_date char(10)
);insert into tempRecordDate
select distinct record_date from tb2;select id, ifnull(num, 0) as num, tmp.record_date
from tempRecordDate as tmp join tb1 left join tb2
on id = tb1_id and tmp.record_date = tb2.record_date
order by tmp.record_date, id
IFNULL(num,0)
FROM (
SELECT DISTINCT id,record_date FROM tb2a,tb1a) a LEFT JOIN tb2a b ON a.id=b.`tb1_id` AND a.record_date=b.`record_date`
mysql> SELECT a.id,a.a.record_date,
-> IFNULL(num,0)
-> FROM (
-> SELECT DISTINCT id,record_date FROM tb2a,tb1a) a LEFT JOIN tb2a b ON a.
=b.`tb1_id` AND a.record_date=b.`record_date`;
+------+-------------+---------------+
| id | record_date | IFNULL(num,0) |
+------+-------------+---------------+
| 1 | 2011-08-15 | 10 |
| 2 | 2011-08-15 | 11 |
| 3 | 2011-08-15 | 0 |
| 1 | 2011-08-16 | 12 |
| 2 | 2011-08-16 | 13 |
| 3 | 2011-08-16 | 14 |
| 1 | 2011-08-17 | 0 |
| 2 | 2011-08-17 | 15 |
| 3 | 2011-08-17 | 0 |
+------+-------------+---------------+
9 rows in set (0.00 sec)mysql>