为您的方案创建一些虚拟数据:
create table product_description
(
product_id varchar(10),product_description varchar(50)
)
create table orders
(
SKU varchar(10),price decimal(10,2),cost_price decimal(10,quantity int,[state] varchar(10),created_at varchar(20) -- Ugly,but you're treating this as a string in your query
)
insert into product_description
values
('PRO001','Sweater'),('PRO002','Jeans'),('PRO003','Shoes'),('PRO004','Dress'),('PRO005','Blouse')
insert into orders
values
('PRO001',29.99,13.50,3,'complete','2019-11-19'),('PRO001',26.99,12.50,1,'2018-06-18'),37.99,20.75,2,'2019-11-17'),19.99,6.50,10,'2019-11-16'),6.25,5,'2019-11-15'),23.99,10.50,13,'2019-11-14'),21.00,'2019-11-13'),7,'incomplete','2019-11-12'),18.99,5.50,9,'2019-11-11'),39.99,23.50,18,11.50,23,'2019-11-10'),12,'2019-11-09')
在这些表中产生以下数据:
select * from product_description
/----------------------------------\
| product_id | product_description |
|------------|---------------------|
| PRO001 | Sweater |
| PRO002 | Jeans |
| PRO003 | Shoes |
| PRO004 | Dress |
| PRO005 | Blouse |
\----------------------------------/
select * from orders
/------------------------------------------------------------------\
| SKU | price | cost_price | quantity | state | created_at |
|--------|-------|------------|----------|------------|------------|
| PRO001 | 29.99 | 13.50 | 3 | complete | 2019-11-19 |
| PRO001 | 26.99 | 12.50 | 1 | complete | 2018-06-18 |
| PRO004 | 37.99 | 20.75 | 2 | complete | 2019-11-17 |
| PRO003 | 19.99 | 6.50 | 10 | complete | 2019-11-16 |
| PRO003 | 19.99 | 6.25 | 5 | complete | 2019-11-15 |
| PRO002 | 23.99 | 10.50 | 13 | complete | 2019-11-14 |
| PRO004 | 37.99 | 21.00 | 3 | complete | 2019-11-13 |
| PRO001 | 29.99 | 13.50 | 7 | incomplete | 2019-11-12 |
| PRO003 | 18.99 | 5.50 | 9 | complete | 2019-11-11 |
| PRO004 | 39.99 | 23.50 | 18 | complete | 2019-11-11 |
| PRO005 | 19.99 | 11.50 | 23 | complete | 2019-11-10 |
| PRO001 | 29.99 | 13.50 | 12 | complete | 2019-11-09 |
\------------------------------------------------------------------/
我相信以下查询会为您提供所需的信息:
select
pd.product_description as Product,sum((o.quantity * o.price) - (o.quantity * o.cost_price)) as GrossMargin
from orders o
left join product_description pd on o.SKU = pd.product_id
group by pd.product_description
结果:
/-----------------------\
| Product | GrossMargin |
|---------|-------------|
| Blouse | 195.27 |
| Dress | 382.27 |
| Jeans | 175.37 |
| Shoes | 325.01 |
| Sweater | 377.27 |
\-----------------------/
如果您想进一步过滤特定年份或特定产品的结果,则可以:
select
pd.product_description as Product,sum((o.quantity * o.price) - (o.quantity * o.cost_price)) as GrossMargin
from orders o
left join product_description pd on o.SKU = pd.product_id
where o.created_at like '2019%'
and pd.product_description = 'Sweater'
group by pd.product_description
您会看到,但不包括2018年以后的订单:
/-----------------------\
| Product | GrossMargin |
|---------|-------------|
| Sweater | 362.78 |
\-----------------------/
本文链接:https://www.f2er.com/3078425.html