我正在尝试根据共享计划输出销售佣金。我有2个问题：
如何在不使用内联子查询的情况下编写查询
如果销售人员和他们未分享100％的佣金，则如何输出。
这是我到目前为止的内容：
DECLARE @SALES TABLE
(
Id INT,
SalesPerson VARCHAR(10),
Commission MONEY
)
INSERT INTO @SALES VALUES(1,'Amy', 100.00)
INSERT INTO @SALES VALUES(2,'Lisa', 200.00)
INSERT INTO @SALES VALUES(3,'Joe', 300.00)
INSERT INTO @SALES VALUES(4,'Wendy', 400.00)
INSERT INTO @SALES VALUES(5,'Mike', 500.00)
DECLARE @SHARE_SCHEDULE TABLE
(
GIVER INT,
TAKER INT,
PERCENTAGE DECIMAL(9,2)
)
INSERT INTO @SHARE_SCHEDULE VALUES(3, 1, .5)
INSERT INTO @SHARE_SCHEDULE VALUES(3, 2, .5)
INSERT INTO @SHARE_SCHEDULE VALUES(4, 5, .4)
SELECT
S.SalesPerson,
S.Commission + (SELECT SUM(Commission)
FROM @SALES
WHERE ID = SS.GIVER) * SS.PERCENTAGE Commission
FROM @SALES S
JOIN @SHARE_SCHEDULE SS ON SS.TAKER = S.ID
预期成绩：
SalesPerson Commission
Amy 250.00
Lisa 350.00
Wendy 240.00
Mike 660.00
最佳答案
with Q as
(
select S.*, coalesce(J.share, 0) received, coalesce(K.share, 0) given from SALES S
outer apply
(
select sum(H.PERCENTAGE * L.Commission) as share from SHARE_SCHEDULE H
inner join SALES L on L.Id = H.GIVER
where H.TAKER = S.Id
) J
outer apply
(
select sum(H.PERCENTAGE * L.Commission) as share from SHARE_SCHEDULE H
inner join SALES L on L.Id = H.GIVER
where H.GIVER = S.Id
) K
)
select SalesPerson, (Commission + received - given) TotalCommission from Q
where (Commission + received - given) > 0
J是销售人员收到的所有共享佣金的总和
K是销售人员提供的所有共享佣金的总和
中间结果（
select * from Q）如下所示：Id SalesPerson Commission received given
--- ----------- ---------- ------------ -----------
1 Amy 100.00 150.000000 0.000000
2 Lisa 200.00 150.000000 0.000000
3 Joe 300.00 0.000000 300.000000
4 Wendy 400.00 0.000000 160.000000
5 Mike 500.00 160.000000 0.000000
最终结果如下所示：
SalesPerson TotalCommission (= commission + received - given)
----------- ---------------
Amy 250.000000
Lisa 350.000000
Wendy 240.000000
Mike 660.000000
Rextester Demo