如何将SQL查询输出作为表用于后续查询？附示例

Great question—reusing the output of your initial CTE (TABLE1) for future queries is a common need, and there are several solid approaches depending on how often you need to access this data and your performance requirements. Here are the most practical options tailored to your scenario:

1. Persist to a Temporary Table #

Temporary tables are ideal if you need to reuse the data across multiple queries in the same session. They’re stored in tempdb and automatically cleaned up when your session ends, making them low-maintenance for session-specific reuse.

Example: #

-- Create a temporary table matching your CTE's output structure
CREATE TABLE #ReusableDescriptions (
    DESCRIPTION VARCHAR(MAX) -- Adjust data type to match your actual column definition
);

-- Insert your CTE results into the temp table
WITH TABLE1 AS (
    SELECT COT.DESCRIPTION 
    FROM CONFIGURABLEOBJECTTYPE COT 
    WHERE COT.CONFIGURABLEOBJECTTYPEID IN (
        SELECT CIO.DAMAGEDCOTEMPLATE 
        FROM CLAIMINSURANCEOBJECT CIO 
        INNER JOIN CLAIMRISKUNIT CRU ON CRU.CLAIMRISKUNITID = CIO.CLAIMRISKUNITID 
        INNER JOIN CLAIM CL ON CL.CLAIMID = CRU.CLAIMID 
        INNER JOIN AGREGATEDPOLICY APO ON CL.POLICYID = APO.POLICYID -- Filled in the missing join condition
    )
)
INSERT INTO #ReusableDescriptions (DESCRIPTION)
SELECT DESCRIPTION FROM TABLE1;

-- Now query the temp table in subsequent queries
SELECT * FROM #ReusableDescriptions WHERE DESCRIPTION LIKE '%damage%';

2. Use a Table Variable #

Table variables work similarly to temp tables but are scoped to the batch or function they’re declared in. They’re often better for smaller datasets where you don’t need complex indexing.

Example: #

DECLARE @ReusableDescriptions TABLE (
    DESCRIPTION VARCHAR(MAX)
);

WITH TABLE1 AS (
    SELECT COT.DESCRIPTION 
    FROM CONFIGURABLEOBJECTTYPE COT 
    WHERE COT.CONFIGURABLEOBJECTTYPEID IN (
        SELECT CIO.DAMAGEDCOTEMPLATE 
        FROM CLAIMINSURANCEOBJECT CIO 
        INNER JOIN CLAIMRISKUNIT CRU ON CRU.CLAIMRISKUNITID = CIO.CLAIMRISKUNITID 
        INNER JOIN CLAIM CL ON CL.CLAIMID = CRU.CLAIMID 
        INNER JOIN AGREGATEDPOLICY APO ON CL.POLICYID = APO.POLICYID
    )
)
INSERT INTO @ReusableDescriptions (DESCRIPTION)
SELECT DESCRIPTION FROM TABLE1;

-- Reuse the table variable later in the same batch
SELECT COUNT(*) AS TotalDescriptions FROM @ReusableDescriptions;

3. Create a View #

If this query logic is something you’ll reuse across multiple sessions or applications, a view is a clean, maintainable choice. Views act as virtual tables and always return the latest data when queried.

Example: #

CREATE VIEW vw_DamagedCOTemplateDescriptions AS
SELECT COT.DESCRIPTION 
FROM CONFIGURABLEOBJECTTYPE COT 
WHERE COT.CONFIGURABLEOBJECTTYPEID IN (
    SELECT CIO.DAMAGEDCOTEMPLATE 
    FROM CLAIMINSURANCEOBJECT CIO 
    INNER JOIN CLAIMRISKUNIT CRU ON CRU.CLAIMRISKUNITID = CIO.CLAIMRISKUNITID 
    INNER JOIN CLAIM CL ON CL.CLAIMID = CRU.CLAIMID 
    INNER JOIN AGREGATEDPOLICY APO ON CL.POLICYID = APO.POLICYID
);

-- Query the view just like any regular table
SELECT * FROM vw_DamagedCOTemplateDescriptions;

4. Chain CTEs for Ad-Hoc Queries #

If you only need to use the CTE results in a single batch of queries, you can chain multiple CTEs together. This avoids persisting data but requires keeping the initial CTE definition in the same query block.

Example: #

WITH TABLE1 AS (
    SELECT COT.DESCRIPTION 
    FROM CONFIGURABLEOBJECTTYPE COT 
    WHERE COT.CONFIGURABLEOBJECTTYPEID IN (
        SELECT CIO.DAMAGEDCOTEMPLATE 
        FROM CLAIMINSURANCEOBJECT CIO 
        INNER JOIN CLAIMRISKUNIT CRU ON CRU.CLAIMRISKUNITID = CIO.CLAIMRISKUNITID 
        INNER JOIN CLAIM CL ON CL.CLAIMID = CRU.CLAIMID 
        INNER JOIN AGREGATEDPOLICY APO ON CL.POLICYID = APO.POLICYID
    )
),
TABLE2 AS (
    -- Reuse TABLE1 in this subsequent CTE
    SELECT DESCRIPTION, COUNT(*) AS DescriptionFrequency
    FROM TABLE1
    GROUP BY DESCRIPTION
)
-- Query the final derived dataset
SELECT * FROM TABLE2 ORDER BY DescriptionFrequency DESC;

Quick Recommendations: #

• Use temporary tables for large datasets or when you need to add indexes to speed up subsequent queries.
• Use table variables for small to medium datasets in a single batch.
• Use views for frequently reused logic that needs to return real-time data.
• Use chained CTEs for one-off, ad-hoc query sequences.

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