基于行数关联CTE的SQL标签值生成技术问询

Alright, I get what you're trying to do here. You want to only apply that 'yes' tag to Inventory records if your filtered CTE has more than a specified number of rows. Here's a straightforward way to pull this off:

First, we need to capture the row count of your CTE, then use that count to conditionally trigger the tag logic. Let's break it down with a concrete example:

Example Code #

-- Set your desired threshold here (change 5 to your target number)
DECLARE @threshold INT = 5;

WITH vTable1 AS (
    -- Your original filtered PartNumber set
    SELECT PartNumber FROM Inventory WHERE Quantity > 1
),
CTERowCount AS (
    -- Get the total number of rows in vTable1
    SELECT COUNT(*) AS TotalRows FROM vTable1
)
SELECT 
    -- Keep your existing tag conditions, then add the conditional check
    [A whole bunch of conditions for tags] + 
    CASE 
        -- Only show 'yes' if CTE has more than threshold rows AND PartNumber exists in vTable1
        WHEN (SELECT TotalRows FROM CTERowCount) > @threshold 
             AND vTable1.PartNumber IS NOT NULL THEN 'yes'
        -- Otherwise, default to 'no'
        ELSE 'no'
    END AS TagValue
FROM Inventory
LEFT JOIN vTable1 ON Inventory.PartNumber = vTable1.PartNumber

How This Works #

1. Threshold Variable: @threshold lets you easily adjust the minimum number of rows required to activate the tag logic. If your SQL dialect doesn't support variables (like some older MySQL versions), you can replace @threshold with a hardcoded number (e.g., 5).
2. CTE Count: The CTERowCount CTE calculates how many rows are in your original vTable1 set. We only need this count once, so it's efficient to compute it separately.
3. Conditional CASE Statement: The CASE first checks if the CTE row count exceeds your threshold. If it does, it then checks if the current Inventory record's PartNumber exists in vTable1—only then does it append 'yes'. If either condition fails, it uses 'no' instead.

Alternative for Dialects Without Variables #

If you can't use variables, just hardcode the threshold directly in the CASE statement:

WITH vTable1 AS (
    SELECT PartNumber FROM Inventory WHERE Quantity > 1
),
CTERowCount AS (
    SELECT COUNT(*) AS TotalRows FROM vTable1
)
SELECT 
    [A whole bunch of conditions for tags] + 
    CASE 
        WHEN (SELECT TotalRows FROM CTERowCount) > 5 -- Hardcoded threshold
             AND vTable1.PartNumber IS NOT NULL THEN 'yes'
        ELSE 'no'
    END AS TagValue
FROM Inventory
LEFT JOIN vTable1 ON Inventory.PartNumber = vTable1.PartNumber

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