概念
创建举例
举例一:
举例二:
创建一张视图view_cust_totalprice,可以通过cust_id条件查出该顾客的所有订单总额
MySQL:
create view view_cust_totalprice as select c.cust_id,sum(c.sum_money) sumMoney from (
select o1.cust_id cust_id,o1.order_num order_num,sum(o2.quantity * o2.item_price) sum_money from orders o1
join orderitems o2 on o1.order_num = o2.order_num
group by o1.order_num
) c group by c.cust_id;
Oracle:
create view view_cust_totalprice as SELECT o1.cust_id cust_id,sum(o2.sum_money) sumMoney FROM orders o1
JOIN (SELECT order_num,sum(QUANTITY * ITEM_PRICE ) sum_money FROM ORDERITEMS GROUP BY order_num) o2
ON o1.order_num = o2.order_num GROUP BY o1.cust_id;
select * from view_cust_totalprice where cust_id = '1000000001';