mysql的left join，求sql语句

[richard_2010]

有两个表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语句？

[WWWWA]

简要说明结果是怎样得出的，估计要用到辅助表

[WWWWA]

为什么没有
0, '2011-08-16'

[zhangyanhe162]

select tb1_id,num,record_date from tb1 group by record_date order by tb1_id;
试下

[richard_2010]

[Quote=引用 2 楼 wwwwa 的回复:]

为什么没有
0, '2011-08-16'
[/Quote]

2011-08-16这天的记录tb1_id是都有的

[richard_2010]

简要的说是需要按天来进行外连接

[nicenight]

mysql 5.1 测试通过：

drop table if exists tempRecordDate;
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

[richard_2010]

[Quote=引用 6 楼 nicenight 的回复:]

mysql 5.1 测试通过：
SQL code

drop table if exists tempRecordDate;
create temporary table tempRecordDate(
record_date char(10)
);

insert into tempRecordDate
select distinct record_date from tb2;

……
[/Quote]

thanks，我去试试

[WWWWA]

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.id=b.`tb1_id` AND a.record_date=b.`record_date`

[WWWWA]

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>