SQL需求：创建临时表并实现正负类别抵消查询

Here's a SQL solution that automatically offsets corresponding positive and negative categories for each Number in your #Temp table, leaving only the net remaining entries:

-- First, create the temp table as specified
CREATE TABLE #Temp(Number Varchar(20), Category Varchar(20));

-- Optional: Insert sample test data to verify the logic
INSERT INTO #Temp VALUES 
('1', 'A'), ('1', '-A'), ('1', '-A'),
('2', 'B'), ('2', 'B'), ('2', '-B'),
('3', '-C'), ('3', '-C'), ('3', 'C'), ('3', 'C'),
('4', 'D'), ('4', 'D'), ('4', 'D'),
('5', '-E'), ('5', '-E');

-- Main query to compute offset results
WITH CategoryCounts AS (
    SELECT 
        Number,
        -- Extract the base category (strip leading '-' from negative entries)
        CASE 
            WHEN LEFT(Category, 1) = '-' THEN SUBSTRING(Category, 2, LEN(Category)) 
            ELSE Category 
        END AS BaseCategory,
        -- Calculate net count: +1 for positive entries, -1 for negative
        SUM(CASE WHEN LEFT(Category, 1) = '-' THEN -1 ELSE 1 END) AS NetCount
    FROM #Temp
    GROUP BY 
        Number, 
        CASE 
            WHEN LEFT(Category, 1) = '-' THEN SUBSTRING(Category, 2, LEN(Category)) 
            ELSE Category 
        END
),
NumberSequence AS (
    -- Generate a sequence of numbers to create multiple rows for net counts >1
    SELECT 1 AS SeqNum
    UNION ALL
    SELECT SeqNum + 1 
    FROM NumberSequence 
    WHERE SeqNum < (SELECT ABS(MAX(NetCount)) FROM CategoryCounts)
)
SELECT 
    cc.Number,
    -- Reconstruct category name (add '-' back if net count is negative)
    CASE WHEN cc.NetCount < 0 THEN '-' + cc.BaseCategory ELSE cc.BaseCategory END AS Category
FROM CategoryCounts cc
CROSS JOIN NumberSequence ns
WHERE ns.SeqNum <= ABS(cc.NetCount)
ORDER BY cc.Number, Category
OPTION (MAXRECURSION 0); -- Allow recursion beyond default 100 if needed

How It Works: #

1. CategoryCounts CTE:

   • Groups entries by Number and the base category (removing the leading '-' from negative categories to match their positive counterparts).
   • Calculates the net count for each group: each positive entry adds 1, each negative subtracts 1. This tells us how many of each category remain after offsetting.

2. NumberSequence CTE:

   • Generates a sequence of integers starting at 1, up to the largest absolute net count across all groups. This lets us create multiple rows for cases where the net count is greater than 1 (e.g., 3 remaining 'D' entries).

3. Final Select:

   • Joins the aggregated counts with the number sequence to generate the correct number of rows for each remaining category.
   • Reconstructs the full category name (adding back the '-' if the net count is negative) and outputs the final offset results.

Sample Output: #

For the test data above, the output will be:

Number | Category
-------|---------
1      | -A
2      | B
4      | D
4      | D
4      | D
5      | -E
5      | -E

This logic handles all scenarios automatically:

• Equal counts of positive and negative categories cancel out entirely (like Number 3 in the sample).
• Remaining entries are kept when counts are unequal (like Numbers 1, 2, 4, 5).

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