商品带来其它商品的销售统计 UPC带货能力 （一）

create table #temp
(
 store_id int,
 channel varchar(10),
 order_id varchar(20),
 upc varchar(20),
 sales int,
 units int
)

insert into #temp(store_id,channel,order_id,upc,sales,units)
values
(1094,'SNG','1000000010','1001',20,2),
(1094,'SNG','1000000010','1002',30,3),
(1094,'SNG','1000000010','1003',40,4),
(1094,'SNG','1000000010','1004',50,5),
(1094,'SNG','1000000010','1005',60,6),

(1094,'SNG','1000000020','1001',30,3),
(1094,'SNG','1000000020','1002',40,4),
(1094,'SNG','1000000020','1003',50,5),

(1094,'SNG','1000000030','1003',50,5),
(1094,'SNG','1000000030','1004',60,6),
(1094,'SNG','1000000030','1005',70,7),

(1094,'SNG','1000000040','1001',50,5),
(1094,'SNG','1000000040','1002',60,6),

(1094,'SNG','1000000050','1004',40,4),

(1094,'SNG','1000000060','1005',50,5)

select * from #temp 

 

select 
 order_id
 ,count(upc) as sum_upc
 ,sum(sales) as sum_sales
 ,sum(units) as sum_units
from #temp
group by order_id


select 
 order_id,upc
from #temp
group by order_id,upc

select a1.*,b1.upc
from (
--1
select 
 order_id
 ,count(upc) as sum_upc
 ,sum(sales) as sum_sales
 ,sum(units) as sum_units
from #temp
group by order_id
--end1
) as a1
inner join (
--2
select 
 order_id,upc
from #temp
group by order_id,upc
--end2
) as b1 on b1.order_id=a1.order_id 

 

select 
 upc
 ,sum(sum_upc)   as sum_upc
 ,sum(sum_sales) as sum_sales
 ,sum(sum_units) as sum_units
from (
select a1.*,b1.upc
from (
select 
 order_id
 ,count(upc) as sum_upc
 ,sum(sales) as sum_sales
 ,sum(units) as sum_units
from #temp
group by order_id
) as a1
inner join (
select 
 order_id,upc
from #temp
group by order_id,upc
) as b1 on b1.order_id=a1.order_id 
) as t
group by upc