22,206
社区成员
发帖
与我相关
我的任务
分享
;WITH a AS (
SELECT tt.*,0 AS newlv FROM tb_test AS tt WHERE tt.title='A点'
UNION ALL
SELECT tt.*,a.newlv+1 AS newlv FROM tb_test AS tt JOIN a ON tt.parent=a.id
)
SELECT SUM(
CASE
WHEN title = 'A点' THEN sales
WHEN newlv = 1 THEN sales * 0.03
WHEN newlv = 2 THEN sales * 0.02
WHEN newlv = 3 THEN sales * 0.01
ELSE 0
END
) AS totalsales
FROM a
OPTION(MAXRECURSION 0)
;WITH ctea AS(
SELECT * ,
1 AS ll
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
b.ll+1 AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
)
SELECT SUM(CASE WHEN ll = 1 THEN ctea.sales
WHEN ll = 2 THEN ctea.sales * 0.03
WHEN ll = 3 THEN ctea.sales * 0.02
WHEN ll = 4 THEN ctea.sales * 0.01
ELSE 0
END)
FROM ctea;
--先找到A下面所有的子节点
;WITH a AS (
SELECT tt.* FROM tb_test AS tt WHERE tt.title='A点'
UNION ALL
SELECT tt.* FROM tb_test AS tt JOIN a ON tt.parent=a.id
)
SELECT SUM(
CASE
WHEN title = 'A点' THEN sales
WHEN lvl = 4 THEN sales * 0.01
WHEN lvl < 4 THEN sales * 0.005
WHEN lvl > 4 THEN 0
END
) AS totalsales
FROM a
OPTION(MAXRECURSION 0)
;WITH ctea AS(
SELECT * ,
1.0 AS [percent]
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
( CASE WHEN a.lvl <> 0 THEN 0.5 --自己有lvl的设置成0.5 自己没有lvl的继承父级的
ELSE b.[percent] --自己没有
END ) AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
WHERE a.lvl < 4 --去掉比A级别高的
)
SELECT ( SELECT sales
FROM ctea
WHERE title = 'A点'
) + SUM(ctea.sales * ctea.[percent] * 0.01)
FROM ctea
;WITH ctea AS(
SELECT * ,
1.0 AS [percent]
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
( CASE WHEN a.lvl <> 0 THEN 0.5 --自己有lvl的设置成0.5 自己没有lvl的继承父级的
ELSE b.[percent] --自己没有
END ) AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
WHERE a.lvl < 4 --去掉比A级别高的
)
SELECT SUM(sales*[percent]) AS sumsales FROM ctea