SQL技术求助：基于临时表生成目标结果表的实现问题

Got it, let's tackle this problem step by step. Since SQL is built for set-based operations (way more efficient than row-by-row traversal for most scenarios), I'll start with the recommended approach, plus a fallback if you absolutely need explicit batch-by-batch processing.

Prerequisites & Assumptions #

First, let's anchor this to common table structures (adjust these to match your actual schema):

• #All_Lots: Has a unique batch identifier, e.g., BatchID INT or BatchNumber VARCHAR(50)
• #All_Data: Contains batch-linked data, with columns like BatchID (to join with #All_Lots), InstructionID (to count instructions), and your incomplete mi_* columns (e.g., mi_Status, mi_CreationDate)

Recommended: Set-Based Approach (No Explicit Traversal) #

This is SQL best practice—avoid cursors unless you have complex per-batch logic that can't be handled with set operations.

Step 1: Create #Final_Data (if not already exists) #

CREATE TABLE #Final_Data (
    BatchID INT PRIMARY KEY, -- Match your batch identifier data type
    BatchNumber VARCHAR(50), -- Include if present in #All_Lots
    InstructionCount INT,
    -- Add columns for your mi_* data (adjust names/types to match your needs):
    mi_LatestStatus VARCHAR(20),
    mi_MaxReading DECIMAL(10,2)
)

Step 2: Insert Aggregated Data into #Final_Data #

This query joins the two temp tables, counts instructions per batch, and pulls relevant mi_* metrics (adjust aggregate functions like MAX()/LAST_VALUE() to fit your actual mi_* data requirements):

INSERT INTO #Final_Data (BatchID, BatchNumber, InstructionCount, mi_LatestStatus, mi_MaxReading)
SELECT
    al.BatchID,
    al.BatchNumber,
    COUNT(DISTINCT ad.InstructionID) AS InstructionCount, -- Use COUNT(*) if every row is a unique instruction
    -- Example mi_* data retrieval (tweak based on your exact needs):
    MAX(CASE WHEN ad.mi_CreationDate = (SELECT MAX(mi_CreationDate) FROM #All_Data ad2 WHERE ad2.BatchID = al.BatchID) THEN ad.mi_Status END) AS mi_LatestStatus,
    MAX(ad.mi_NumericReading) AS mi_MaxReading
FROM #All_Lots al
LEFT JOIN #All_Data ad ON al.BatchID = ad.BatchID
GROUP BY al.BatchID, al.BatchNumber

Fallback: Cursor-Based Traversal (For Complex Per-Batch Logic) #

If you have custom logic that requires processing each batch one at a time (e.g., conditional updates, multi-step calculations), use a cursor:

-- Declare variables to hold batch data
DECLARE @BatchID INT, @BatchNumber VARCHAR(50)
DECLARE @InstructionCount INT
DECLARE @mi_LatestStatus VARCHAR(20), @mi_MaxReading DECIMAL(10,2)

-- Create cursor to iterate over all batches
DECLARE BatchCursor CURSOR FOR
SELECT BatchID, BatchNumber FROM #All_Lots

OPEN BatchCursor
FETCH NEXT FROM BatchCursor INTO @BatchID, @BatchNumber

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Calculate instruction count for the current batch
    SELECT @InstructionCount = COUNT(DISTINCT InstructionID) FROM #All_Data WHERE BatchID = @BatchID

    -- Retrieve mi_* data for the current batch (adjust logic as needed)
    SELECT 
        @mi_LatestStatus = MAX(CASE WHEN mi_CreationDate = (SELECT MAX(mi_CreationDate) FROM #All_Data WHERE BatchID = @BatchID) THEN mi_Status END),
        @mi_MaxReading = MAX(mi_NumericReading)
    FROM #All_Data WHERE BatchID = @BatchID

    -- Insert results into #Final_Data
    INSERT INTO #Final_Data (BatchID, BatchNumber, InstructionCount, mi_LatestStatus, mi_MaxReading)
    VALUES (@BatchID, @BatchNumber, @InstructionCount, @mi_LatestStatus, @mi_MaxReading)

    FETCH NEXT FROM BatchCursor INTO @BatchID, @BatchNumber
END

-- Clean up cursor
CLOSE BatchCursor
DEALLOCATE BatchCursor

Key Adjustments for Your Use Case #

• Replace placeholder mi_* columns with your actual data fields. If you need more specific logic for these columns (e.g., pulling the first mi-value, filtering mi-records by a condition), share those details and I can refine the query.
• Add indexes to #All_Data.BatchID and #All_Lots.BatchID to speed up joins/filters, especially if your datasets are large.
• Swap COUNT(DISTINCT InstructionID) with COUNT(*) if every row in #All_Data corresponds to a unique instruction.

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