create table t1
(
t1_id serial primary key,
type integer,
show_begintime date,
show_endtime date
)insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
create table t2
(
t2_id serial primary key,
t1_id integer references t1 (t1_id),
show_begintime date,
show_endtime date
)insert into t1(t1_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-01-31');
insert into t1(t1_id,show_begintime,show_endtime)values(2,'2009-02-01','2009-02-28');
insert into t1(t1_id,show_begintime,show_endtime)values(3,'2009-03-01','2009-03-31');
insert into t1(t1_id,show_begintime,show_endtime)values(4,'2009-04-01','2009-04-30');
insert into t1(t1_id,show_begintime,show_endtime)values(6,'2009-06-01','2009-06-30');
insert into t1(t1_id,show_begintime,show_endtime)values(7,'2009-07-01','2009-07-31');
insert into t1(t1_id,show_begintime,show_endtime)values(8,'2009-08-01','2009-08-31');
insert into t1(t1_id,show_begintime,show_endtime)values(9,'2009-09-01','2009-09-30');
(
t1_id serial primary key,
type integer,
show_begintime date,
show_endtime date
)insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
create table t2
(
t2_id serial primary key,
t1_id integer references t1 (t1_id),
show_begintime date,
show_endtime date
)insert into t1(t1_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-01-31');
insert into t1(t1_id,show_begintime,show_endtime)values(2,'2009-02-01','2009-02-28');
insert into t1(t1_id,show_begintime,show_endtime)values(3,'2009-03-01','2009-03-31');
insert into t1(t1_id,show_begintime,show_endtime)values(4,'2009-04-01','2009-04-30');
insert into t1(t1_id,show_begintime,show_endtime)values(6,'2009-06-01','2009-06-30');
insert into t1(t1_id,show_begintime,show_endtime)values(7,'2009-07-01','2009-07-31');
insert into t1(t1_id,show_begintime,show_endtime)values(8,'2009-08-01','2009-08-31');
insert into t1(t1_id,show_begintime,show_endtime)values(9,'2009-09-01','2009-09-30');
(
t1_id serial primary key,
type integer,
show_begintime date,
show_endtime date
)insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(1,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
insert into t1(type,show_begintime,show_endtime)values(2,'2009-07-01','2009-12-30');
create table t2
(
t2_id serial primary key,
t1_id integer references t1 (t1_id),
show_begintime date,
show_endtime date
)insert into t1(t1_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-01-31');
insert into t1(t1_id,show_begintime,show_endtime)values(2,'2009-02-01','2009-02-28');
insert into t1(t1_id,show_begintime,show_endtime)values(3,'2009-03-01','2009-03-31');
insert into t1(t1_id,show_begintime,show_endtime)values(4,'2009-04-01','2009-04-30');
insert into t1(t1_id,show_begintime,show_endtime)values(6,'2009-06-01','2009-06-30');
insert into t1(t1_id,show_begintime,show_endtime)values(7,'2009-07-01','2009-07-31');
insert into t1(t1_id,show_begintime,show_endtime)values(8,'2009-08-01','2009-08-31');
insert into t1(t1_id,show_begintime,show_endtime)values(9,'2009-09-01','2009-09-30');在t1表中找出type=1的所有記錄,但要與t2匹配,如果t2表存在 t1.t1_id=t2.t1_id 此條記錄,則提取t2表中的數據,反之就取t1的數據我想要的結果集是:t1_id | show_begintime | show_endtime
1 | 2009-01-01 | 2009-01-31
2 | 2009-02-01 | 2009-02-28
3 | 2009-03-01 | 2009-03-31
4 | 2009-04-01 | 2009-04-30
5 | 2009-07-01 | 2009-12-30
-> COALESCE(t2.show_begintime,t1.show_begintime) as show_begintime,
-> COALESCE(t2.show_endtime,t1.show_endtime) as show_endtime
-> from t1 left join t2 on t1.t1_id=t2.t1_id
-> where t1.type=1;
+-------+----------------+--------------+
| t1_id | show_begintime | show_endtime |
+-------+----------------+--------------+
| 1 | 2009-01-01 | 2009-01-31 |
| 2 | 2009-02-01 | 2009-02-28 |
| 3 | 2009-03-01 | 2009-03-31 |
| 4 | 2009-04-01 | 2009-04-30 |
| 5 | 2009-07-01 | 2009-12-30 |
+-------+----------------+--------------+
5 rows in set (0.00 sec)mysql>