我发现了一些东西,但看起来相当恶心。使用递归函数可以更轻松地完成这个任务...
DECLARE @Table TABLE(
ID INT,
Op VARCHAR(20),
ParentID INT
)
INSERT INTO @Table SELECT 1,'OR',NULL
INSERT INTO @Table SELECT 2,'AND',1
INSERT INTO @Table SELECT 3,'AND',1
INSERT INTO @Table SELECT 4,'>',2
INSERT INTO @Table SELECT 5,'a',4
INSERT INTO @Table SELECT 6,'alpha',4
INSERT INTO @Table SELECT 7,'>',2
INSERT INTO @Table SELECT 8,'b',7
INSERT INTO @Table SELECT 9,'beta',7
INSERT INTO @Table SELECT 10,'>',3
INSERT INTO @Table SELECT 11,'c',10
INSERT INTO @Table SELECT 12,'gamma',10
INSERT INTO @Table SELECT 13,'<',3
INSERT INTO @Table SELECT 14,'a',13
INSERT INTO @Table SELECT 15,'delta',13
;WITH Vals AS (
SELECT t.*,
1 Depth
FROM @Table t LEFT JOIN
@Table parent ON t.ID = parent.ParentID
WHERE parent.ParentID IS NULL
UNION ALL
SELECT t.*,
v.Depth + 1
FROM @Table t INNER JOIN
Vals v ON v.ParentID = t.ID
),
ValLR AS(
SELECT DISTINCT
vLeft.ID LeftID,
vLeft.Op LeftOp,
vRight.ID RightID,
vRight.Op RightOp,
vLeft.ParentID OperationID,
vLeft.Depth
FROM Vals vLeft INNER JOIN
Vals vRight ON vLeft.ParentID = vRight.ParentID
AND vLeft.ID < vRight.ID
WHERE (vRight.ID IS NOT NULL)
),
ConcatVals AS(
SELECT CAST('(' + LeftOp + ' ' + Op + ' ' + RightOp + ')' AS VARCHAR(500)) ConcatOp,
t.ID OpID,
v.Depth,
1 CurrentDepth
FROM ValLR v INNER JOIN
@Table t ON v.OperationID = t.ID
WHERE v.Depth = 1
UNION ALL
SELECT CAST('(' + cL.ConcatOp + ' ' + t.Op + ' {' + CAST(v.RightID AS VARCHAR(10)) + '})' AS VARCHAR(500)) ConcatOp,
t.ID OpID,
v.Depth,
cL.CurrentDepth + 1
FROM ValLR v INNER JOIN
@Table t ON v.OperationID = t.ID INNER JOIN
ConcatVals cL ON v.LeftID = cL.OpID
WHERE v.Depth = cL.CurrentDepth + 1
),
Replaces AS(
SELECT REPLACE(
c.ConcatOp,
SUBSTRING(c.ConcatOp,PATINDEX('%{%', c.ConcatOp), PATINDEX('%}%', c.ConcatOp) - PATINDEX('%{%', c.ConcatOp) + 1),
(SELECT ConcatOp FROM ConcatVals WHERE OpID = CAST(SUBSTRING(c.ConcatOp,PATINDEX('%{%', c.ConcatOp) + 1, PATINDEX('%}%', c.ConcatOp) - PATINDEX('%{%', c.ConcatOp) - 1) AS INT))
) ConcatOp,
1 Num
FROM ConcatVals c
WHERE Depth = (SELECT MAX(Depth) FROM ConcatVals)
UNION ALL
SELECT REPLACE(
r.ConcatOp,
SUBSTRING(r.ConcatOp,PATINDEX('%{%', r.ConcatOp), PATINDEX('%}%', r.ConcatOp) - PATINDEX('%{%', r.ConcatOp) + 1),
(SELECT ConcatOp FROM ConcatVals WHERE OpID = CAST(SUBSTRING(r.ConcatOp,PATINDEX('%{%', r.ConcatOp) + 1, PATINDEX('%}%', r.ConcatOp) - PATINDEX('%{%', r.ConcatOp) - 1) AS INT))
) ConcatOp,
Num + 1
FROM Replaces r
WHERE PATINDEX('%{%', r.ConcatOp) > 0
)
SELECT TOP 1
*
FROM Replaces
ORDER BY Num DESC
输出
ConcatOp
(((a > alpha) AND (b > beta)) OR ((c > gamma) AND (a < delta)))
如果你更喜欢看递归函数,请告诉我,我们可以一起看看。
编辑:递归函数
看看这个有多简单。
CREATE TABLE TableValues (
ID INT,
Op VARCHAR(20),
ParentID INT
)
INSERT INTO TableValues SELECT 1,'OR',NULL
INSERT INTO TableValues SELECT 2,'AND',1
INSERT INTO TableValues SELECT 3,'AND',1
INSERT INTO TableValues SELECT 4,'>',2
INSERT INTO TableValues SELECT 5,'a',4
INSERT INTO TableValues SELECT 6,'alpha',4
INSERT INTO TableValues SELECT 7,'>',2
INSERT INTO TableValues SELECT 8,'b',7
INSERT INTO TableValues SELECT 9,'beta',7
INSERT INTO TableValues SELECT 10,'>',3
INSERT INTO TableValues SELECT 11,'c',10
INSERT INTO TableValues SELECT 12,'gamma',10
INSERT INTO TableValues SELECT 13,'<',3
INSERT INTO TableValues SELECT 14,'a',13
INSERT INTO TableValues SELECT 15,'delta',13
GO
CREATE FUNCTION ReturnMathVals (@ParentID INT, @Side VARCHAR(1))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @RetVal VARCHAR(500)
IF (@ParentID IS NULL)
BEGIN
SELECT @RetVal = ' (' + dbo.ReturnMathVals(ID,'L') + Op + dbo.ReturnMathVals(ID,'R') + ') '
FROM TableValues
WHERE ParentID IS NULL
END
ELSE
BEGIN
SELECT TOP 1 @RetVal = ' (' + dbo.ReturnMathVals(ID,'L') + Op + dbo.ReturnMathVals(ID,'R') + ') '
FROM TableValues
WHERE ParentID = @ParentID
ORDER BY CASE WHEN @Side = 'L' THEN ID ELSE -ID END
SET @RetVal = ISNULL(@RetVal, (SELECT TOP 1 Op FROM TableValues WHERE ParentID = @ParentID ORDER BY CASE WHEN @Side = 'L' THEN ID ELSE -ID END))
END
RETURN @RetVal
END
GO
SELECT dbo.ReturnMathVals(NULL, NULL)
GO
DROP FUNCTION ReturnMathVals
DROP TABLE TableValues