递归SQL查询根据角色获取金额 [英] Recursive SQL query to get amount according to the role
问题描述
以下是我的表格：
Following are my tables:
**[UserMaster]**
UserId Int, UserName Varchar(200),AddedBy Int
UserId EmpName AddedBy
1 admin 0
2 SubAdmin1 1
3 Vikas 2
4 Mohit 3
5 Atul 4
6 Vishal 2
7 Mani 3
8 Sunny 1
[SalesMaster]
SalesId Int, UserId Int (FK_UserMaster_UserId) , Price Int
SalesId UserId Price StatusId**
10 1 100 1
11 2 200 1
12 3 300 1
13 4 500 1
14 5 100 2
15 6 200 1
16 7 111 2
17 8 222 1
18 1 50 1
19 2 20 1
[SalesProducts]
SaleProductId Int, SaleId Int (FK_SaleMaster_SaleId) , Quantity Int
SaleProductId SalesId Quantity
1 10 2
2 11 2
3 12 1
4 13 1
5 14 3
6 15 2
7 16 5
8 17 1
9 18 1
10 19 1
** [输出] **
结果集应具有登录用户的销售额+由用户创建的用户他和他们的
**[OUTPUT]**
The result set should have the sales of the login user + the users created by him and the further users created by the their
sub users where SalesMaster.StatusId=@StatusId
以下TotalSalesAmount是：
[SalesMaster] .Price * [SalesProducts] .Quantity
例如：UserId的总销售额：1，其中SalesMaster.StatusId = 1：
100 * 2 + 200 * 2 + 300 * 1 + 500 * 1 + 200 * 2 + 222 * 1 + 50 * 1 + 20 * 1 = 2092
类似于UserId：2是SalesMaster.StatusId = 1
200 * 2 + 300 * 1 + 200 * 2 + 500 * 1 + 200 * 2 + 20 * 1 = 2020
案例1：
让我们来SalesMaster.StatusId = 1，我们需要显示以下内容：
The following TotalSalesAmount is:
[SalesMaster].Price * [SalesProducts].Quantity
For eg: Total Sales amount for UserId: 1 , where SalesMaster.StatusId=1 is :
100*2+200*2+ 300*1 + 500*1 + 200*2 + 222*1 + 50*1+20*1= 2092
Similarly for UserId: 2 is where SalesMaster.StatusId=1
200*2+300*1 + 200*2 +500*1 + 200*2+20*1= 2020
case 1:
Lets Take SalesMaster.StatusId= 1, We need to show following:
UserId TotalSalesAmount OwnSaleAmount AddedBy
1 2092 250 0
2 2020 420 1
3 800 300 2
4 500 500 3
5 0 0 4
6 200 200 2
7 0 0 3
8 222 222 1
案例2：
如果（Vikas）UserId = 3将登录SalesMaster.StatusId =@StatusId，
让我们去SalesMaster.StatusId = 2，我们需要显示以下内容：
Case 2:
In case (Vikas)UserId=3 will login where SalesMaster.StatusId=@StatusId,
Lets Take SalesMaster.StatusId= 2, We need to show following:
UserId TotalSalesAmount OwnSaleAmount AddedBy
3 100*3+111*5=855 0 2
4 300 0 3
5 100*3=300 300 4
7 111*5=555 555 3
我运行了这个查询但是给出了错误的TotalSaleAmount和OwnSaleAmount结果。
I have this query running but giving wrong "TotalSaleAmount" and "OwnSaleAmount" results.
with tblOwnSaleAmount as
(
select UM.UserId, UM.AddedBy, sum( SM.Price*SP.Quantity ) as OwnSaleAmount
from UserMaster UM
left join SalesMaster SM on SM.UserId = UM.UserId and SM.statusID = 1
left join SalesProducts SP on SP.SaleId = SM.SalesId
where UM.UserId = 2
group by UM.UserId ,UM.AddedBy
union all
select UM.UserId, UM.AddedBy,
case when SM.statusID = 1 then ( SM.Price*SP.Quantity )
else 0 end as OwnSaleAmount
from UserMaster UM
join tblOwnSaleAmount on tblOwnSaleAmount.UserId = UM.AddedBy
join SalesMaster SM on SM.UserId = UM.UserId
join SalesProducts SP on SP.SaleId = SM.SalesId
),
tbldistinctOwnSaleAmount as
(
select distinct UserId, AddedBy, OwnSaleAmount
from tblOwnSaleAmount
) ,
tblTotalOwnSaleAmount as
(
select OSA.UserId,OSA.AddedBy, sum( OSA.OwnSaleAmount ) as OwnSaleAmount
from tbldistinctOwnSaleAmount OSA
group by OSA.UserId ,OSA.AddedBy
)
select OSA.*
, TotalSalesAmount = isnull(
(
select sum( OSA1.OwnSaleAmount )
from tblTotalOwnSaleAmount OSA1
where OSA1.UserId > OSA.UserId
), 0 )
+ OSA.OwnSaleAmount
from tblTotalOwnSaleAmount OSA
order by OSA.UserId
问题在于计算TotalSalesAmount的最后状态，
它总结了所有用户ownSaleAmount，其中OSA1.UserId> = OSA.UserId。因为在一些用户之间没有他们的孩子
请帮助
The problem is in last statememt of calculating the TotalSalesAmount,
It sums up all the Users ownSaleAmount where OSA1.UserId >= OSA.UserId. As in between some users dont have their child
please help
推荐答案
我认为这行代码是完全错误的：
I think this line of your code is completely wrong:
where OSA1.UserId > = OSA.UserId or OSA1.AddedBy = OSA.UserId or (OSA1.StatusId=@StatusId and OSA.StatusId=@StatusId)
你过滤了
You filtered
OSA1.AddedBy = OSA.UserId
你的递归
，我认为你只需要过滤
in your recursion, i think you need only to filter
OSA1.UserId > = OSA.UserId and OSA1.StatusId=@StatusId
将子查询更改为
change the sub query to
select sum( OSA1.OwnSaleAmount )
from tblOwnSaleAmount OSA1
where OSA1.UserId > = OSA.UserId and OSA1.StatusId=@StatusId and OSA.StatusId=@StatusId)
)
我希望能解决问题。
I hope to solve the problem.
这篇关于递归SQL查询根据角色获取金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
