表定義如下，
專案定義表 T_Item,其中mode=0代表取公式計算值，mode=1代表直接取資料值
公式計算表T_Rule（關鍵在于可能有多層嵌套，而且順序可能錯亂，CAL=1代表加法，CAL=-1代表減法）
和基礎資料T_Data
要求查詢T_Item中所有專案的值
CREATE TABLE #T_Item
(NAME VARCHAR(50),
Mode int)
INSERT INTO #T_Item
SELECT 'A01',1 UNION ALL
SELECT 'A02',1 UNION ALL
SELECT 'A03',1 UNION ALL
SELECT 'A04',1 UNION ALL
SELECT 'A05',1 UNION ALL
SELECT 'B1',0 UNION ALL
SELECT 'B2',0 UNION ALL
SELECT 'B3',0
CREATE TABLE #T_Rule
(NAME VARCHAR(50),
RowVALUE VARCHAR(50)，
CAL int)
INSERT INTO #T_Rule
SELECT 'B1','A01',1 UNION ALL
SELECT 'B1','A02',1 UNION ALL
SELECT 'B1','A03',-1 UNION ALL
SELECT 'B2','B1',1 UNION ALL
SELECT 'B2','A04',-1 UNION ALL
SELECT 'B3','A05',1 UNION ALL
SELECT 'B3','B1',1 UNION ALL
SELECT 'B3','B2',1
CREATE TABLE #T_Data
( NAME VARCHAR(50),
Value float)
INSERT INTO #T_Data
SELECT 'A01',100 UNION ALL
SELECT 'A02',50 UNION ALL
SELECT 'A03',70 UNION ALL
SELECT 'A04',100
期望得到的結果
A01 100
A02 50
A03 70
A04 100
A05 0
B1 80
B2 -20
B3 60
uj5u.com熱心網友回復：
感覺自己現在的寫法有點low，看看大神們的思路，學習一下uj5u.com熱心網友回復：
或者有可以優化基礎表結構的想法也可以uj5u.com熱心網友回復：
WITH CTE
AS
(SELECT *,RowVALUE AS RowVALUE_LV1,CAL AS CAL_LV1 FROM #T_Rule A
WHERE EXISTS (SELECT 1 FROM #T_Data WHERE A.RowVALUE=https://bbs.csdn.net/topics/NAME)
UNION ALL
SELECT A.*,B.RowVALUE_LV1,B.CAL_LV1
FROM #T_Rule AS A
JOIN CTE AS B ON A.RowVALUE=https://bbs.csdn.net/topics/B.NAME)
SELECT A.NAME,SUM(ISNULL(C.Value,0)*ISNULL(B.CAL_LV1,1)) AS RESULT
FROM #T_Item AS A
LEFT JOIN CTE AS B ON A.NAME=B.NAME
LEFT JOIN #T_Data AS C ON ISNULL(B.RowVALUE_LV1,A.NAME)=C.NAME
GROUP BY A.NAME
ORDER BY A.NAME
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/270374.html
標籤：基礎類
下一篇：資料庫備份失敗錯誤