SQL面试题

一个简单的table有100条以上的信息，其中包括

产品    颜色   数量

产品1   红色   112

产品1   蓝色   142

产品2   红色   234

产品2   蓝色   null

产品3   红色   444

产品1   红色   120

产品3   红色   null

。。。。。。。。。。

 

请用SQL 完成

1、按产品分类，列出各类产品中红色数量大于蓝色数量的产品名称和差额数量。

 

2、按产品分类，按下面格式进行统计

     产品 红色 蓝色

解答：

create table products 
(
pname varchar2(20),
pcolor varchar2(20),
pnum  number
);

insert into products values('产品1','红色',112);
insert into products values('产品1','蓝色',142);
insert into products values('产品2','红色',234);
insert into products values('产品2','蓝色',null);
insert into products values('产品3','红色',444);
insert into products values('产品1','红色',120);
insert into products values('产品3','红色',null);
commit;

 

1、参考答案

select p1.pname,(p1.sum1-p2.sum2) as 差额 from 
(
select pname,pcolor,sum(nvl(pnum,0)) sum1 from products 
where pcolor = '红色'
group by pname,pcolor
) p1 
left join 
(
select pname,pcolor,sum(nvl(pnum,0)) sum2 from products 
where pcolor = '蓝色'
group by pname,pcolor
) p2
on p1.pname = p2.pname
where p1.sum1 > p2.sum2
union all
select pname,sum(pnum) as 差额 from products
where pcolor = '红色'  and pname not in 
(
  select pname from products where pcolor = '蓝色' group by pname
)
group by pname

    结果：

    PNAME    差额

     产品1         90

     产品2        234

     产品3        444

 

2、参考答案

select p1.pname as 产品,nvl(p1.sum1,0) as 红色 , nvl(p2.sum2,0) as 蓝色 from 
(
select pname,pcolor,sum(nvl(pnum,0)) sum1 from products 
where pcolor = '红色'
group by pname,pcolor
) p1 
full join 
(
select pname,pcolor,sum(nvl(pnum,0)) sum2 from products 
where pcolor = '蓝色'
group by pname,pcolor
) p2
on p1.pname = p2.pname

 

    结果：

    产品     红色      蓝色

    产品1    232      142

    产品2    234         0

    产品3    444         0