Oracle sql 复习题目总结(4)

1、查询酒类商品的总点击量

select '酒类' category_name,sum(goods_click_num) total_click from t_goods
where goods_category in (
    select category_id from t_category
    where category_name='酒类'
);

2、查询每个类别所属商品的总点击量，并按降序排列

select a.goods_category,b.category_name,sum(a.goods_click_num) total_click
from t_goods a inner join t_category b on a.goods_category = b.category_id
group by goods_category,category_name
order by sum(a.goods_click_num) desc;

3、 查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列

select c.category_id,c.category_name,b.goods_no,b.goods_name,a.max_click
from (
    select goods_category,max(goods_click_num) max_click from t_goods
    group by goods_category
)a,t_goods b,t_category c
where a.goods_category=b.goods_category 
and a.max_click=b.goods_click_num 
and c.category_id=a.goods_category
order by a.max_click desc;

4、查询茅台的销售情况,按日期升序排列

select d.goods_no,d.goods_name,e.category_name,c.*
from
(
    select max(a.goods_no) goods_no,a.sale_date,a.quantity day_quantity,max(a.amount) day_amount,sum(b.quantity) total_quantity,sum(b.amount) total_amount
    from t_saleinfo a inner join t_saleinfo b
    on a.goods_no=b.goods_no and a.goods_no=(
        select goods_no from t_goods where goods_name='贵州茅台'
    ) and a.sale_date>=b.sale_date
    group by a.sale_date,a.quantity
    order by a.sale_date
)c,t_goods d,t_category e
where c.goods_no=d.goods_no and d.goods_category=e.category_id;

（编辑：ASP站长网）