DM数据库，sql根据用户订单来推送喜欢购买的商品类型中销量最好的两种商品

DM数据库，sql根据用户订单来推送喜欢购买的商品类型中销量最好的两种商品

注:不考虑用户购买多种类型的商品数量相同和销量相同的情况（最喜欢的商品类型不唯一，销量最好的两种商品不唯一）

select top 2 t.sale_num,k.* from (

	select sum(goods_amount)sale_num,a.goods_num,father_num from i_order a,goods b,goods_type c 
	where a.goods_num=b.num and b.type_num=c.num group by father_num,a.goods_num) t,goods k
	 
	where t.goods_num=k.num and t.father_num=(
	
	select father_num from goods_type where num=(
		select type_num from goods where num=(
			select top 1 goods_num from i_order 
			where phone=15112345678 group by goods_num order by sum(goods_amount) desc)))
order by sale_num desc;

需要调用三张表：商品表、商品种类表、订单表

A） 确定用户喜欢的一级商品类型

思路：
    1） 从i_order表中得到用户购买数量最多的商品编码；
    2) 根据商品编码在goods表中找到对应的商品二级类型编码；
    3) 根据商品二级类型编码在goods_type表中找到对应的商品一级类型编码，即最喜欢的商品类型。

注：phone是i_order表的主键，代表用户，这儿以phone=151123456789为例：

1）用where筛选出对象用户的数据，再以商品编号分组，用聚合函数sum统计商品的总购买量并以此为排序列降序排列，再取第一行数据，同时只取商品编号列

select top 1 goods_num from i_order 
where phone=15112345678 group by goods_num order by sum(goods_amount) desc;

2）使用子查询

select type_num from goods where num=(
		select top 1 goods_num from i_order 
		where phone=15112345678 group by goods_num order by sum(goods_amount) desc
);

3）使用子查询

select father_num from goods_type where num=(
	select type_num from goods where num=(
		select top 1 goods_num from i_order 
		where phone=15112345678 group by goods_num order by sum(goods_amount) desc
));

效果：

B）统计每种一级类型的商品销量

思路：
    1) 多表联查一级类型添加到订单；
    2) 对查询结果进行分组聚合，统计每种一级类型的商品销量。

1. 订单中的销量要与一级类型在同一个表中，三表联查，将i_order中的商品编号与goods表中的商品编号相连，goods表中的二级类型编号与goods_type表中的二级类型编号相连。

select a.*,father_num from i_order a,goods b,goods_type c 
where a.goods_num=b.num and b.type_num=c.num;

2. 先将查询结果以一级类型分组，再以商品编号分组，用聚合函数sum求出商品销量。

select sum(goods_amount)sale_num,a.goods_num,father_num from i_order a,goods b,goods_type c 
where a.goods_num=b.num and b.type_num=c.num group by father_num,a.goods_num;

效果：

C）将B表与A值进行关联

1. 将查询结果与用户喜欢的类型进行关联，得出用户喜欢的那一种类型的订单信息，并以销量降序排列；

select * from (

	select sum(goods_amount)sale_num,a.goods_num,father_num from i_order a,goods b,goods_type c 
	where a.goods_num=b.num and b.type_num=c.num group by father_num,a.goods_num) t 
	
	where t.father_num=(
	
	select father_num from goods_type where num=(
		select type_num from goods where num=(
			select top 1 goods_num from i_order 
			where phone=15112345678 group by goods_num order by sum(goods_amount) desc)))
	order by sale_num desc;

效果：

2. 再将上面的查询结果与goods表通过商品编号关联，进行多表联查，丰富推送的商品信息。再将已经以销量降序排列后的数据取前两行。

select top 2 t.sale_num,k.* from (

	select sum(goods_amount)sale_num,a.goods_num,father_num from i_order a,goods b,goods_type c 
	where a.goods_num=b.num and b.type_num=c.num group by father_num,a.goods_num) t,goods k
	 
	where t.goods_num=k.num and t.father_num=(
	
	select father_num from goods_type where num=(
		select type_num from goods where num=(
			select top 1 goods_num from i_order 
			where phone=15112345678 group by goods_num order by sum(goods_amount) desc)))
order by sale_num desc;

效果：