按销售人员计算销售额
按销售人员计算销售额
提问于 2019-03-15 03:05:54
我有一张以下格式的桌子。
样本表与数据
declare @sales table
(productid int, TransDate date,SalesPerson varchar(100),Amount bigint)
insert into @sales
values
(1 , 20150101, 50 , 'S1'),
(1 , 20150201, 50 , 'S1'),
(1 , 20150301, 50 , 'S1'),
(1 , 20150401, 50 , 'S1'),
(1 , 20150501, 50 , 'S1'),
(1 , 20150601, 50 , 'S2'),
(1 , 20150701, 50 , 'S2'),现在，我需要计算每个销售代表的总销售额和相关的销售(除主要销售人员以外的所有销售人员执行的销售)。
例如，对于销售人员S1，他的销售额为250个，相关代理(S2)销售(也参与S1为产品1提供服务的销售人员)为100。
产出应是：
SalesPerson Date MySalesAmount RelatedSalesPersonSalesAmount
S1 20150101 50 0
S1 20150201 50 0
S1 20150301 50 0
S1 20150401 50 0
S1 20150501 50 0
S1 20150601 0 50
S1 20150701 0 50
S2 20150101 0 50
S2 20150201 0 50
S2 20150301 0 50
S2 20150401 0 50
S2 20150501 0 50
S2 20150601 50 0
S2 20150701 50 0有人能在这方面提供帮助，通过查询实现上述输出吗？
回答 4
Stack Overflow用户
回答已采纳
发布于 2019-03-15 05:15:35
请检查以下查询：
declare @sales table
(productid int, TransDate date,SalesPerson varchar(100),Amount bigint);
insert into @sales(productid, TransDate, Amount, SalesPerson)
values
(1, '20150101', 50, 'S1'),
(1, '20150201', 50, 'S1'),
(1, '20150301', 50, 'S1'),
(1, '20150401', 50, 'S1'),
(1, '20150501', 50, 'S1'),
(1, '20150601', 50, 'S2'),
(1, '20150701', 50, 'S2'),
(1, '20150601', 10, 'S3'),
(1, '20150701', 10, 'S3');
select distinct
SalesPerson,
sum(Amount) over (partition by productid, SalesPerson) as MySalesAmount,
sum(Amount) over (partition by productid) -
sum(Amount) over (partition by productid, SalesPerson) as RelatedSalesPersonSalesAmount
from @sales;输出：
+-------------+---------------+-------------------------------+
| SalesPerson | MySalesAmount | RelatedSalesPersonSalesAmount |
+-------------+---------------+-------------------------------+
| S1 | 250 | 120 |
| S2 | 100 | 270 |
| S3 | 20 | 350 |
+-------------+---------------+-------------------------------+用雷克斯试验器在线测试。
更新：
select
x.productid,
x.SalesPerson,
y.TransDate as [Date],
iif(x.SalesPerson = y.SalesPerson, y.Amount, 0) as MySalesAmount,
iif(x.SalesPerson = y.SalesPerson, 0, y.Amount) as RelatedSalesPersonSalesAmount
from (select distinct productid, SalesPerson from @sales) as x
join @sales as y
on x.productid = y.productid
order by x.productid, x.SalesPerson;输出：
+-----------+-------------+---------------------+---------------+-------------------------------+
| productid | SalesPerson | Date | MySalesAmount | RelatedSalesPersonSalesAmount |
+-----------+-------------+---------------------+---------------+-------------------------------+
| 1 | S1 | 01.01.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.02.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.03.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.04.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.05.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.06.2015 00:00:00 | 0 | 50 |
| 1 | S1 | 01.07.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.01.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.02.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.03.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.04.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.05.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.06.2015 00:00:00 | 50 | 0 |
| 1 | S2 | 01.07.2015 00:00:00 | 50 | 0 |
+-----------+-------------+---------------------+---------------+-------------------------------+Stack Overflow用户
发布于 2019-03-15 03:15:00
这似乎很复杂。很明显，销售人员的数量是相当容易的：
select salesperson, sum(salesamt)
from t
group by salesperson;对于相关金额，我们需要销售金额的其他销售人员谁已经销售相同的产品。
我认为自我加入才是拯救之道：
select t.salesperson,
sum(case when t.salesperson = t2.salesperson then t.salesamnt end) as mysales,
sum(case when t.salesperson <> t2.salesperson then t.salesamnt end) as othersales
from t join
t t2
on t.product = t2.product
group by t.salespersonStack Overflow用户
发布于 2019-03-15 04:02:45
试试这个-
declare @sales table
(productid int, transDate date, amount bigint, salesperson varchar(100))
insert into @sales
values
(1 , '20150101', 50 , 'S1'),
(1 , '20150201', 50 , 'S1'),
(1 , '20150301', 50 , 'S1'),
(1 , '20150401', 50 , 'S1'),
(1 , '20150501', 50 , 'S1'),
(1 , '20150601', 50 , 'S2'),
(1 , '20150701', 50 , 'S2')
;with cteSales as
(select s.salesperson, productid, sum(amount) amount
from @sales s
group by s.salesperson, productid)
select s1.salesperson, s1.productid,
sum(case when s1.salesperson = s2.salesperson then s1.amount else 0 end) as mysales,
sum(case when s1.salesperson <> s2.salesperson then s2.amount else 0 end) as othersales
from cteSales s1 inner join cteSales s2
on s1.productid = s2.productid
group by s1.salesperson, s1.productid页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55174971
复制相关文章
相似问题
腾讯云开发者
