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');
解决方案 »
- 求助 mysqld 启动的时候 执行 init_fle
- mysqldump 怎么写的
- 用Mysql还原.sql文件?.sql文件在其他文件夹
- 求MySQL按某一类别过滤出前N行的方法
- 求解高效的sql语句 ,mysql交集问题
- RSS订阅添加到数据库,如何覆盖原先的数据?
- 如何在Windows上安装两个Mysql服务
- 我在某书上看到“MySQL是基于web的数据库”,请问这个“基于web”是什么意思?
- 下载了一个mysql administrator但是不知道怎么用
- 关于resin服务器自带mysql的jdbc驱动程序的问题
- 会员数据查询大于30造成服务器假死
- SQLYog企业版怎么连接内网mysql数据库?
(
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>