ORACLE1.10 - 一对多

-- 一对多 （订单order,订单明细orderDetail）

-- 主从表

-- 订单表

create table t_order (

   order_id number primary key,

   today date,

   desk_num varchar2(10)

);

insert into t_order(order_id,today,desk_num)

values(1,to_date('2018-03-12','yyyy-MM-dd'),'A05');

 

insert into t_order(order_id,today,desk_num)

values(2,to_date('2018-03-12','yyyy-MM-dd'),'B04');

 

insert into t_order(order_id,today,desk_num)

values(3,to_date('2018-03-11','yyyy-MM-dd'),'B01');

 

commit

select * from t_order

 

 

-- 订单明细表

drop table t_order_detail

create table t_order_detail(

   id number primary key,

   order_id number,  -- 关系

   food_name varchar2(20),-- 食品名字

   unit_name varchar2(20),-- 单位

   price number(6,2),  --单价

   nums number         --数量

);

 

insert into t_order_detail

(id,order_id,food_name, unit_name, price,nums)

values

(1,1,'麻婆豆腐','块',1.5,12);

insert into t_order_detail

(id,order_id,food_name, unit_name, price,nums)

values

(2,1,'东坡肉','块',15,3);

 

insert into t_order_detail

(id,order_id,food_name, unit_name, price,nums)

values

(3,2,'麻婆豆腐','块',1.5,5);

insert into t_order_detail

(id,order_id,food_name, unit_name, price,nums)

values

(4,2,'上海青','斤',6.5,2);

insert into t_order_detail

(id,order_id,food_name, unit_name, price,nums)

values

(5,2,'香干火锅肉','斤',16.5,2);

commit

 

select * from t_order

select * from t_order_detail

 

-- 今天赚了多少钱

select to_char(sysdate,'yyyy-MM-dd') from dual

-- 找出2张订单ID

select order_id from t_order

where

to_char(today,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')

 

select sum(price*nums) as 总收入 from t_order_detail

where order_id in(

   select order_id from t_order

   where

   to_char(today,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')

)

 

 

--本质：内关联

select * from t_order,t_order_detail

where t_order.order_id=t_order_detail.order_id

 

--左关联（左为主，右为辅）

select * from t_order

left join t_order_detail

on

t_order.order_id=t_order_detail.order_id