05-4.7继查询语句和视图.pptVIP

4.6 典型查询实例分析;4.6.1 不同选择条件的聚合列的查询;一）使用两个视图;二）使用一个视图及NULL的运用;二）使用一个视图及NULL的运用-改进1;建立视图 create view avggradeunion(eleid,boygrade,girlgrade,boycnt,girlcnt) as select se.eleid,avg(se.grade),0,count(*),0 from se ,student s where se.stdid=s.stdid and s.stdsex=1 group by se.eleid union select se.eleid,0,avg(se.grade),0,count(*) from se ,student s where se.stdid=s.stdid and s.stdsex=0 group by se.eleid 查询语句 select eleid, sum(case when boycnt0 then boygrade else null end), sum(case when girlcnt0 then girlgrade else null end) from avggradeunion group by eleid;三）不使用视图;四）使用一个视图的改进方法;4.6.2 一行数据归属多个分组的查询;Select a.saledate,b.goodsno,b.saleqty,b.salepricefrom salesummary a,saledetail bwhere a.saleno=b.saleno;汇总查询：年月、品号、月累计销售数、年累计销售数;;建表：存在销售的年月表saleexistyearmon(saleyear,salemon)或建立视图;将销售表和saleexistyearmon连接;查询：年累计销售数;统计：月累计销售数;1.根据销售表统计月累计销售数;语句二;语句三;同时输出月累计销售数和年累计销售数;一、使用两个视图 视图1：统计各年各月各商品的月累计销售数 create view msq(saleyear,salemon,goodsno,qty) as select year(a.saledate),month(a.saledate),b.goodsno,sum(b.saleqty) from salesummary a,saledetail b where a.saleno=b.saleno group by year(a.saledate),month(a.saledate),b.goodsno 视图2：统计各年各月各商品的年累计销售数 create view ysq(saleyear,salemon,goodsno,qty) as select a.saleyear,a.salemon,c.goodsno,sum(c.saleqty) from v_saleexistyearmon a,salesummary b,saledetail c where year(b.saledate)=a.saleyear and month(b.saledate)=a.salemon and b.saleno=c.saleno group by a.saleyear,a.salemon,c.goodsno 连接上述两个视图： select a.saleyear,a.salemon,a.goodsno,a.qty,b.qty from msq a,ysq b where a.saleyear=b.saleyear and a.salemon=b.salemon and a.goodsno=b.goodsno;二、联合两个查询并定义为一个视图，然后对视图分组统计 定义视图： create view saleqty(saleyear,salemon,goodsno,monqty,yearqty) as select year(a.saledate),month(a.saledate),b.goodsno,sum(b.saleqty),0 from salesummary a,saledetail b where a.saleno=b.saleno group by year(a.saledate),month(a.saledate),b.goodsno union select a.saleyear,a.salemon,c.goodsno,0,sum(c.saleqty) from v_saleexistyearmon a,salesummary b,saledetail c