使用T-SQL CTE实现递归查询遇阻，寻求技术解决方案

Hey Patrick, let's work through this recursive query problem you're stuck on. First, let's recap your tables clearly to align on the data we're working with:

Table 1: Product List (product_list) #

Table 2: Product Price (product_price) #

Based on your mention of recursive queries and the ordered product lists, I assume your goal is to recursively traverse each List group in the order of SortNumber, while linking each product to its relevant price data (since your price table has duplicate entries per product, we'll first normalize that to pick a specific price, like the highest one).

Here's a step-by-step solution using SQL recursive CTEs (Common Table Expressions), which is the standard approach for recursive queries:

-- First, preprocess the price table to get a single price per product+type+client (e.g., highest price)
WITH ranked_prices AS (
    SELECT 
        ProductType,
        Client,
        ProductCode,
        Price,
        -- Rank prices descending, so rank=1 is the highest price
        ROW_NUMBER() OVER (
            PARTITION BY ProductType, Client, ProductCode 
            ORDER BY Price DESC
        ) AS price_rank
    FROM product_price
),
-- Recursive CTE to traverse each product list in sort order
recursive_product_traversal AS (
    -- Anchor member: Grab the first product (SortNumber=1) for each List
    SELECT 
        pl.List,
        pl.ProductCode,
        pl.SortNumber,
        rp.Price,
        rp.ProductType,
        rp.Client
    FROM product_list pl
    LEFT JOIN ranked_prices rp 
        ON pl.ProductCode = rp.ProductCode
        AND rp.price_rank = 1  -- Pick the highest price entry
    WHERE pl.SortNumber = 1

    UNION ALL

    -- Recursive member: Get the next product in the same List (SortNumber +1)
    SELECT 
        pl.List,
        pl.ProductCode,
        pl.SortNumber,
        rp.Price,
        rp.ProductType,
        rp.Client
    FROM recursive_product_traversal rpt
    JOIN product_list pl 
        ON rpt.List = pl.List
        AND pl.SortNumber = rpt.SortNumber + 1
    LEFT JOIN ranked_prices rp 
        ON pl.ProductCode = rp.ProductCode
        AND rp.price_rank = 1
)
-- Final output: All products traversed recursively, ordered by List and SortNumber
SELECT * FROM recursive_product_traversal
ORDER BY List, SortNumber;

Key Notes & Adjustments: #

• Price Selection: If you don't want the highest price, adjust the ORDER BY in the ranked_prices CTE. For example, if you have a PriceDate field and want the latest price, use ORDER BY PriceDate DESC instead.
• Handling Missing Prices: The LEFT JOIN ensures products without a price entry still show up (with NULL for price fields). If you only want products with existing prices, switch to INNER JOIN.
• Recursion Termination: The recursion stops automatically when there's no next product (i.e., no SortNumber = current SortNumber +1 for the same List).

If your recursive goal is different (e.g., traversing product dependencies instead of ordered lists), let me know and we can tweak this further!

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