sql - 使用 CTE 从下线记录中获取总计数和总金额之和

Question

我们如何使用 SQL 对所有下线行进行计数并求和到其上线。

当前数据:

  ST_ID    UPLINE   AMOUNT
  ---------------------------
  44930   52001    400
  52016   52001    300
  52001   9024     432
  76985   9024     100
  12123   35119    234
  12642   35119    213
  12332   23141    654

在上表中，uplinedata 52001 两个 ST_ID，金额为 400 和 300 code> 的总和为 700，ST_ID 的总金额为 52001，金额为 400，因此总金额为 5201 > 将是 400 + 300 + 432 = 1132 并且上线 9024 的 ST_ID 为 52001 且 432 + 700 总共 1132。

预期输出:

  UPLINE   AMOUNT  CNT
  ------------------------
  52001    1132    2       (400 +300 + 432 | 1+1+1)
  9024     1232    4       (700 + 432 + 100 | 2+1+1 = 4)
  35119    447     2       (234 + 213 | 1+1 = 2)
  23141    654     1

我想到了递归 CTE，但无法收集逻辑。有谁有任何想法来实现这一目标。我正在使用 SQL Server 2016。

Answer 1

据我了解，Upline 列连接到 ST_ID 列，您想要查找按(Upline + ST_ID 中的所有匹配值)分组的总和和计数。即，Upline = 9024 连接到 ST_ID = 52001，因此 Upline = 9024 的总和将为(9024 中的 432 + 100 加上 52001 中的 300 + 400)。

您可以使用递归 CTE，如下所示:

With CTE As
(
  Select ST_ID, Upline, Amount From table_name
  Union All
  Select T.ST_ID, T.Upline, C.Amount
  From table_name T Join CTE C
  On C.Upline = T.ST_ID
)
Select Upline, 
       Sum(Amount) As Amount, 
       Count(*) As Cnt
From CTE
Group By Upline

查看demo .

根据新的要求进行更新(除了之前查询的总和之外，还添加 ST_ID=Upline 的值的总和):

With CTE As
(
  Select * From table_name
  Union All
  Select T.ST_ID, T.Upline, C.AMOUNT
  From table_name T Join CTE C
  On C.Upline = T.ST_ID
)
Select C.Upline, 
       Sum(C.Amount) + ISNULL(Sum(Distinct T.Amount), 0) As Amount, 
       Count(*) + Count(Distinct T.Amount) As Cnt
From CTE C Left Join table_name T
On C.Upline = T.ST_ID
Group By C.Upline

参见demo .