一个面试题

create table tb_product
(
prod_id int ,   --商品编号
category_id int, ----商品类别
prod_name varchar(100)    ----商品名称


)


create table tb_sales
(
prod_id int ,   ---商品编号
num int,         ----销售数量
price float     -----销售单价

)

tb_product  表数据 商品表数据

1 1 1 背心
 2 2 1裤衩
3 3 2面包
4 4 2牛奶
5 5 1衬衣
6 6 1内裤

tb_sales  表数据 销售表数据

1 1 1 1.2
2 1 21.3
3 2 15
4 2 36
5 3 11
6 3 11.5
7 4 11
8 4 11
9 6 11

1、查询销售数量排行前十位的商品编号、名称、及总数量、总价格

select * from 
(
select a.prod_name,a.prod_id,case when  b.shuliang  is NULL then 0 else shuliang end shuliang ,  case when   b.zongjia is NULL then 0 else zongjia   end zongjia  from tb_product a left join 


(select prod_id,sum(num) shuliang, sum(num*price) zongjia  from tb_sales  group by  prod_id) b on a.prod_id=b.prod_id  order by shuliang desc  
)
 where rownum<10

2、查询每类商品中销售数量排行前三的商品编号、商品名称、 商品类别

select * from (


select a.category_id,a.prod_name,a.prod_id,case when  b.shuliang  is NULL then 0 else shuliang end shuliang ,  case when   b.zongjia is NULL then 0 else zongjia   end zongjia  from tb_product a left join 


(select prod_id, sum(num) shuliang, sum(num*price) zongjia  from tb_sales  group by  prod_id) b on a.prod_id=b.prod_id  order by shuliang desc  ) s1 where s1. shuliang


 in (  
 
 select shuliang from 
 
 
 (
 select a.category_id,a.prod_name,a.prod_id,case when  b.shuliang  is NULL then 0 else shuliang end shuliang ,  case when   b.zongjia is NULL then 0 else zongjia   end zongjia  from tb_product a left join 


(select prod_id, sum(num) shuliang, sum(num*price) zongjia  from tb_sales  group by  prod_id) b on a.prod_id=b.prod_id  order by shuliang desc 
 
) s2 where s1.category_id=s2.category_id  and rownum <=3
 
 ) order by s1.category_id,s1.shuliang desc