请求将指定Oracle递归SQL语句转换为SQL Server兼容语句

Got it, let's start by clarifying what your original Oracle query does: it generates all unique permutations of the string 'ABC' by recursively combining characters without repeating any, then formats the result to remove the leading slash from the concatenated path.

Here's the equivalent SQL Server implementation, with explanations of how it maps to your original code:

Full Working Query #

WITH chars AS (
    -- Replace Oracle's dual+CONNECT BY LEVEL: generate each character with its position
    SELECT 
        pos = number,
        letter = SUBSTRING('ABC', number, 1)
    FROM master..spt_values
    WHERE type = 'P' AND number BETWEEN 1 AND LEN('ABC')
),
permutations AS (
    -- Anchor member: start with each individual character (matches your initial subquery)
    SELECT 
        word = letter,
        used_positions = CAST(pos AS VARCHAR(100)) -- Track which positions we've used to avoid duplicates
    FROM chars
    UNION ALL
    -- Recursive member: build permutations by adding unused characters (matches Oracle's CONNECT BY NOCYCLE)
    SELECT 
        word = p.word + '/' + c.letter,
        used_positions = p.used_positions + ',' + CAST(c.pos AS VARCHAR(100))
    FROM permutations p
    JOIN chars c 
        ON CHARINDEX(',' + CAST(c.pos AS VARCHAR(100)) + ',', ',' + p.used_positions + ',') = 0
)
-- Final output: remove slashes and filter only full-length permutations (matches your WHERE clause)
SELECT REPLACE(word, '/', '') AS word
FROM permutations
WHERE LEN(word) = LEN('ABC') + (LEN('ABC') - 1) -- Account for the slashes between characters
ORDER BY word;

Key Mapping to Your Original Oracle Code #

• Oracle's dual + CONNECT BY LEVEL: Replaced with master..spt_values (a system table with sequential numbers) to generate rows for each character in the string. This works for any length string, not just 'ABC'.
• Oracle's SYS_CONNECT_BY_PATH: We build the path manually in the recursive CTE by concatenating characters with '/', then use REPLACE at the end to clean it up—just like your original query.
• Oracle's CONNECT BY NOCYCLE lvl != PRIOR lvl: The used_positions column tracks which character positions we've already included in a permutation. The CHARINDEX check ensures we only add characters that haven't been used yet, preventing cycles and duplicates.

Make It Reusable #

If you want to test with different strings, just replace 'ABC' with a variable:

DECLARE @inputString VARCHAR(100) = 'XYZ'; -- Swap this with your target string

WITH chars AS (
    SELECT 
        pos = number,
        letter = SUBSTRING(@inputString, number, 1)
    FROM master..spt_values
    WHERE type = 'P' AND number BETWEEN 1 AND LEN(@inputString)
),
permutations AS (
    SELECT 
        word = letter,
        used_positions = CAST(pos AS VARCHAR(100))
    FROM chars
    UNION ALL
    SELECT 
        word = p.word + '/' + c.letter,
        used_positions = p.used_positions + ',' + CAST(c.pos AS VARCHAR(100))
    FROM permutations p
    JOIN chars c 
        ON CHARINDEX(',' + CAST(c.pos AS VARCHAR(100)) + ',', ',' + p.used_positions + ',') = 0
)
SELECT REPLACE(word, '/', '') AS word
FROM permutations
WHERE LEN(word) = LEN(@inputString) + (LEN(@inputString) - 1)
ORDER BY word;

内容的提问来源于stack exchange，提问作者Hm Ch