Project definition table T_Item, including mode=0 represents the formula to calculate value, mode=1 represent directly the data value
Formula table T_Rule (the key is there may be multiple nested, and order may be disorder, CAL=1 represents addition, CAL=1 on behalf of the subtraction)
And basic data T_Data
For all the projects in the query T_Item value
The CREATE TABLE # T_Item
(the NAME VARCHAR (50),
Mode int)
INSERT INTO # T_Item
SELECT 'A01 and 1 UNION ALL
SELECT 'how A02, 1 UNION ALL
SELECT 'A03, 1 UNION ALL
SELECT 'A04, 1 UNION ALL
SELECT 'A05, 1 UNION ALL
UNION ALL SELECT 'B1, 0
SELECT 'B2, 0 UNION ALL
SELECT 'B3, 0
The CREATE TABLE # T_Rule
(the NAME VARCHAR (50),
RowVALUE VARCHAR (50),
CAL int)
INSERT INTO # T_Rule
SELECT 'B1', 'A01, 1 UNION ALL
SELECT 'B1', 'how 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
The CREATE TABLE # T_Data
(the NAME VARCHAR (50),
The Value float)
INSERT INTO # T_Data
SELECT 'A01, 100 UNION ALL
SELECT 'how A02, 50 UNION ALL
SELECT 'A03, 70 UNION ALL
SELECT 'A04, 100
Expect the result of the
A01 100
How A02 50
A03 70
A04 100
A05 0
B1 80
B2-20
B3 60
CodePudding user response:
Feel now writing is a bit low, take a look at the thinking of the great god, to learnCodePudding user response:
Or a base table structure can be optimized ideas can alsoCodePudding user response:
WITH CTE
AS
(SELECT *, RowVALUE AS RowVALUE_LV1, CAL AS CAL_LV1 FROM # T_Rule A
WHERE the EXISTS (SELECT 1 FROM # T_Data WHERE A.RowVALUE=https://bbs.csdn.net/topics/NAME)
UNION ALL
SELECT A. *, B.R owVALUE_LV1, biggest AL_LV1
The FROM # T_Rule AS A
The JOIN CTE AS B ON A.RowVALUE=https://bbs.csdn.net/topics/B.NAME)
The SELECT A.N AME, SUM (ISNULL (c. alue, 0) * ISNULL (biggest AL_LV1, 1)) AS the RESULT
The FROM # T_Item AS A
LEFT the JOIN CTE AS B ON A.N AME=B.N AME
LEFT the JOIN # T_Data AS C ON ISNULL (B.R owVALUE_LV1, A.N AME)=C.N AME
GROUP BY A.N AME
The ORDER BY A.N AME