Question

oracle多表之间的查询sql问题

11月表a（business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count）
12月表b（business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count）
现在想要create table c，得到表 c 的内容是：表a和表b中符合以下条件的全部数据：insert_time>=to_date('20141125','yyyymmdd')
and insert_time<=to_date('20141216','yyyymmdd')
and business_id>0
and mail_type_id=1 ;
sql要怎么写....谢谢。

Answer 1

脚本如下：
insert into c
select * from
(select business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count
from a
union all
select business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count
from b) as p
where p.insert_time>=to_date('20141125','yyyymmdd')
and p.insert_time<=to_date('20141216','yyyymmdd')
and p.business_id>0
and p.mail_type_id=1 ;

追问

p是哪个表？

追答

p是下面这两个合并以后的表

select business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count
from a
union all
select business_id,order_id,user_number,mail_type_id,channel_id,service_id,insert_time,read_count
from b

Answer 2

create table c as
select * from a
where insert_time>=to_date('20141125','yyyymmdd')
and insert_time<=to_date('20141216','yyyymmdd')
and business_id>0
and mail_type_id=1
union all
select * from b
where insert_time>=to_date('20141125','yyyymmdd')
and insert_time<=to_date('20141216','yyyymmdd')
and business_id>0
and mail_type_id=1

Answer 3

CREATE TABLE C AS
SELECT * FROM (select * from 表a UNION ALL select * from 表b) A
WHERE insert_time>=to_date('20141125','yyyymmdd')
and insert_time<=to_date('20141216','yyyymmdd')
and business_id>0
and mail_type_id=1 ;