SQL查詢或帶條件邏輯的行內表值函式中的累積與分層計算？-有解無憂

我正在處理兩個資料表，但使用不同的方法來得出以下兩個不同的所需輸出。

第一個是累積層計算，第二個只是基于范圍的分層查找。我需要能夠基于某些 JOINS 使用其他維度表（即 Accounts and Regions 和 Tier Type）的外鍵，為行專案事務回傳此計算。例如，單個帳戶可以根據區域和/或帳戶的 ID 進行累積或分層計算。

鏈接到資料庫小提琴

型別標識	名稱
1	累計
2	分層

分層表：

帳戶ID	型別標識	區域 ID	層號	最小	最大限度	總計 A	總B
101	1	2	1	0	10000	.90	.10
101	1	2	2	10001	30000	.60	.40
101	1	2	3	30001	100000	.40	.60
101	1	2	4	100001	500000	.40	.60
101	1	2	5	500001	999999999999	.20	.80
102	1	3	1	0	7800	.80	.20
102	1	3	2	7801	12800	.70	.30
102	1	3	3	12801	34000	.60	.40
102	1	3	4	34001	50000	.50	.50
102	1	3	5	5000 1	999999999999	.50	.50
103	2	1	1	0	10000	.90	.10
103	2	1	2	10001	30000	.60	.40
103	2	1	3	30001	100000	.40	.60
103	2	1	4	100001	500000	.40	.60
103	2	1	5	500001	999999999999	.20	.80

當前表示例：

跨身份證	帳戶ID	型別標識	區域 ID	總金額
100001	101	1	2	42650
100002	102	1	3	42650
100003	103	2	1	42650

期望的輸出：

跨身份證	帳戶ID	型別標識	區域 ID	總金額	總計 %	總澳元	凈額
100001	101	1	2	42650	0.611	26059.99	16589.99
100002	102	1	3	42650	0.628	26784.98	15864.99
100003	103	2	1	42650	0.40	17060.00	25590.00

我已經能夠對以前的帖子進行一些編輯以獲取帳戶，但似乎無法弄清楚 TransID 100003 中分層查找值的邏輯。

理想情況下，我更喜歡在表值函式（或兩個）中創建此邏輯，然后將其合并到一個視圖中，我將使用該視圖使用 C# 在 Web 表單中進行報告。

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM #temp c
INNER JOIN Accounts a ON a.[Account ID] = c.[Account ID]
CROSS APPLY (
    SELECT
      Total = SUM((v.ActualMax - t.[Min]) * t.[Total A %])
    FROM [dbo].[Tiered Table] t
    CROSS APPLY (VALUES(
      CASE WHEN c.GrossAmt < t.[Max] THEN c.GrossAmt ELSE t.[Max] END
    )) v(ActualMax)
    WHERE c.GrossAmt > t.[Min] AND t.[Account ID] = c.[Account ID]
) t;

任何想法或指導都會非常有幫助和贊賞。

uj5u.com熱心網友回復：

這似乎是一個簡單的AND OR邏輯問題。你需要排除的是行TypeID = 2，也有他們的最大以下的水平GrossAmt。

然后，您只需有條件地匯總總金額（僅對于分層行，將只有一行）或僅該層的金額（對于累積層）。

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM CurrentData c
INNER JOIN Accounts a ON a.[AccountID] = c.[AccountID]
CROSS APPLY (
    SELECT
      Total = SUM(CASE WHEN t.TypeID = 2 THEN v.GrossAmt ELSE (v.ActualMax - t.[Min]) END * t.[Total A])
    FROM [dbo].[Tiers] t
    CROSS APPLY (VALUES(
      CASE WHEN c.GrossAmt < t.[Max] THEN c.GrossAmt ELSE t.[Max] END,
      c.GrossAmt
    )) v(ActualMax, GrossAmt)
    WHERE t.[AccountID] = c.[AccountID]
      AND t.TypeID = c.TypeID
      AND t.RegionID = c.RegionID
      AND c.GrossAmt > t.[Min]
      AND (t.TypeID = 1 OR c.GrossAmt <= t.Max)
) t;

資料庫<>小提琴

第二個CROSS APPLY是必要的，因為聚合外部值。如果將它放在函式中，則不需要它，如上一個問題所示。

請注意，您應該在此處使用半開間隔。換句話說，要么Min或Max應該是排他性的。否則，可能會有值“落空”。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397707.html

標籤：sql sql-server 查询语句

上一篇：一張表的簡單SQL查詢

下一篇：匯出應用oracleapex