sql分组相关

 

/*  create table Inventory (
     item  varchar2(100),
     color varchar2(100),
     quantity number(10)
  )*/
  /*
 
  insert into Inventory values('Table','Blue',124);
  insert into Inventory values('Table','Red',223);
  insert into Inventory values('Chair','Blue',101);
  insert into Inventory values('Chair','Red',null);
  insert into Inventory values('Chair','Red',210); */
 
 
  1、红色商品的数量总和
     select  color, sum(nvl(quantity,0)) from Inventory a where a.color='Red' group by a.color;
 
  2、按Item分类，列出各类商品的数量
     select  item, sum(nvl(quantity,0)) from Inventory a group by a.item;
 
  3、按Item分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量
     select  a.item,  nvl(a.quantity,0)-nvl(b.quantity,0) from Inventory a, Inventory b
                                                           where nvl(a.quantity,0)>nvl(b.quantity,0)  and  a.color='Red'
                                                           and b.color='Blue' and a.item=b.item ;
                                                          
  4、按Item分类，将数据按下列方式进行统计显示
         Item  Red  Blue
    select item , sum(nvl(Red,0)) as red , sum(nvl(Blue,0)) as Blue from (select item,decode(color,'Red',quantity,null) as Red  ,decode(color,'Blue',quantity,null) as Blue 
                                                                           from  Inventory) a
                                                                     group by  a.item  order by item desc