sql 实现业绩分配,求达人解决
[解决办法]
select aa.deptid,aa.count1+bb.count2
(select b.deptid,(a.SumPrice+a.Price)*a.Rate as count1 from 销售订单表 a,用户表 b where a.RateUserID=b.UserID group by b.deptid) aa
,
(select a.deptid,(a.SumPrice+a.Price)*(1-a.Rate) as count2 from 销售订单表 a group by a.deptid) bb
where aa.deptid=bb.deptid
[解决办法]
use Tempdb
go
--> -->
declare @A table([SumPrice] int,[Price] int,[UserID] int,[DeptID] int,[RateUserID] int,[Rate] decimal(18,2))
Insert @A
select 900,100,3,1010,15,0.30 union all
select 300,0,8,1011,0,0 union all
select 800,200,14,1012,0,0
declare @B table([UserID] int,[DeptID] int)
Insert @B
select 3,1010 union all
select 4,1010 union all
select 8,1011 union all
select 15,1012 union all
select 14,1012
select
[DeptID],sum(销售总额) as 销售总额
from (
Select a.[DeptID],sum((a.[SumPrice]+a.[Price])*(1-[Rate])) as 销售总额
from @A as a
group by a.[DeptID]
union all
select b.[DeptID], sum((a.[SumPrice]+a.[Price])*[Rate])
from @A as a
inner join @B as b on a.[RateUserID]=b.[UserID]
group by b.[DeptID]
) as a
group by [DeptID]
/*
DeptID销售总额
1010700.00
1011300.00
10121300.00
*/
----------------------------
-- Author :TravyLee(两情若是久长时，又岂在朝朝暮暮！)
-- Date :2013-04-09 11:33:31
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
--Jul 9 2008 14:17:44
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null
drop table [A]
go
create table [A]
(
[SumPrice] int,
[Price] int,
[UserID] int,
[DeptID] int,
[RateUserID] int,
[Rate] numeric(3,2)
)
insert [A]
select 900,100,3,1010,15,0.30 union all
select 300,0,8,1011,0,0 union all
select 800,200,14,1012,0,0
--> 测试数据：[B]
if object_id('[B]') is not null
drop table [B]
go
create table [B]
(
[UserID] int,
[DeptID] int
)
insert [B]
select 3,1010 union all
select 4,1010 union all
select 8,1011 union all
select 15,1012 union all
select 14,1012
go
;with t
as
(
select
[SumPrice],
[Price],
[UserID],
1-[Rate] as [Rate]
from A
union all
select
[SumPrice],
[Price],
[RateUserID],
[Rate]
from A
where [RateUserID]<>0
)
select
B.DeptID,
sum((a.SumPrice+a.Price)*[Rate]) as SumPrice
from
t a inner join B on a.UserID=B.UserID
group by
B.DeptID
/*
DeptIDSumPrice
------------------------------------
1010700.00
1011300.00
10121300.00
*/
