SELECT m.id 销售主表id,
m.商品名称,
m.销售额,
ISNULL(d1.提成金额,0) 经理提成金额,
ISNULL(d2.提成金额,0) 店长提成金额
FROM 销售主表 m
LEFT JOIN 提成明细表 d1
ON m.id = d1.主表id
AND d1.提成类型 = '经理提成'
LEFT JOIN 提成明细表 d2
ON m.id = d2.主表id
AND d2.提成类型 = '店长提成'
m.商品名称,
m.销售额,
ISNULL(d1.提成金额,0) 经理提成金额,
ISNULL(d2.提成金额,0) 店长提成金额
FROM 销售主表 m
LEFT JOIN 提成明细表 d1
ON m.id = d1.主表id
AND d1.提成类型 = '经理提成'
LEFT JOIN 提成明细表 d2
ON m.id = d2.主表id
AND d2.提成类型 = '店长提成'
(
select 1 as id,'商品1' as 商品名称,1000 as 销售额 union all
select 2 as id,'商品2' as 商品名称,2000 as 销售额 union all
select 3 as id,'商品3' as 商品名称,3000 as 销售额
),提成明细表(id,主表id,提成类型,提成金额,提成人) as
(
select 1,1,'经理提成',100,'张经理' union all
select 2,1,'店长提成',50,'李店长' union all
select 3,2,'经理提成',150,'刘经理' union all
select 4,2,'店长提成',80,'孙店长'
) select * from (select b.id,b.商品名称,销售额,提成金额,提成类型 from 提成明细表 as a right join 销售主表 as b on a.主表id=b.id) as c pivot (max(提成金额)
for 提成类型 in ([经理提成],[店长提成])) as d
/**
id 商品名称 销售额 经理提成 店长提成
--------------------------------------------
1 商品1 1000 100 50
2 商品2 2000 150 80
3 商品3 3000 NULL NULL
---------------------------------------------
**/
Select * Into #A From
(
Select 1 As Id,'商品1' As [商品名称], 1000 As [销售额] Union All
Select 2,'商品2', 2000 Union All
Select 3,'商品2', 3000
) TSelect * Into #B From
(
Select 1 As Id, 1 As [主表Id],'经理提成' As [提成类型],100 As [提成金额],'张经理' As [提成人] Union All
Select 2,1,'店长提成',50,'李店长'Union All
Select 3,2,'经理提成',150,'刘经理' Union All
Select 4,2,'店长提成',80,'孙店长'
) T查询语句:
;With Cte As
(
Select #A.Id,#A.[商品名称],#A.[销售额],#B.[提成类型],#B.[提成金额],#B.[提成人]
From #A Left Join #B ON #A.Id=#B.[主表Id]
)
Select Id,[商品名称],[销售额],SUM(CASE WHEN [提成类型]='经理提成' Then [提成金额] Else 0 End) As 经理提成金额,SUM(CASE WHEN [提成类型]='店长提成' Then [提成金额] Else 0 End) As 店长提成金额
From Cte
Group By Id,[商品名称],[销售额]
查询结果:
Id 商品名称 销售额 经理提成金额 店长提成金额
----------- ----- ----------- ----------- -----------
1 商品1 1000 100 50
2 商品2 2000 150 80
3 商品2 3000 0 0(3 行受影响)