SQL Server视图中特殊规则计算列的实现问询

First, let's clarify the core rule to avoid ambiguity:
For each row in the same Column1 group:

• If there does NOT exist any smaller Value where that smaller value's double (i.e., its potential Calculated value) is greater than the current row's Value, set Calculated to Value * 2.
• Otherwise, set Calculated to 0.

This logic inherently ensures that the maximum Calculated value per Column1 group is at least half of every Value in the group (since the row with the smallest Value will always have Calculated = Value*2, which is double the smallest value—so all larger values' halves will be ≤ that max Calculated).

The SQL Query (Works in Views, No ORDER BY Needed) #

Since we're working with a view, we can't use ORDER BY directly, but a correlated subquery with EXISTS is perfect here to check the condition for each row:

SELECT
    Column1,
    Column2,
    Value,
    CASE
        -- Check if there's any smaller Value in the same group where Value*2 > current Value
        WHEN EXISTS (
            SELECT 1
            FROM test_table2020 t2
            WHERE t2.Column1 = t1.Column1
              AND t2.Value < t1.Value
              AND t2.Value * 2 > t1.Value
        ) THEN 0
        ELSE t1.Value * 2
    END AS Calculated
FROM test_table2020 t1;

Let's Verify Against Your Sample Data #

Let's walk through a few key groups to confirm the output:

• Group Column1=1:
  • Row with Value=10: No smaller values exist → Calculated=20.
  • Rows with Value=12,13,22,23,24: All have smaller values (like 10 or 12) where Value*2 exceeds their own value → Calculated=0.
  • Max Calculated is 20, which is ≥ half of every Value in the group (e.g., 24/2=12 ≤20).
• Group Column1=3:
  • Row with Value=16: No smaller values → Calculated=32.
  • Rows with Value=28,31: 16*2=32 > 28 and 31 → Calculated=0.
  • Max Calculated=32 ≥ 31/2=15.5.
• Group Column1=4 (two rows with Value=9):
  • No smaller values in the group → both rows have Calculated=18. Max is 18 ≥9/2=4.5.

Why This Works in a View #

This query doesn't use ORDER BY (which is restricted in views unless paired with TOP/OFFSET), so it's fully compatible with creating a view from it. The correlated subquery efficiently checks the condition for each row without requiring explicit ordering.

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