Oracle SQL单查询实现：按ID筛选INDEX≥2或无则取INDEX<2行

Got it, let's solve this with a single Oracle query using window functions—they're perfect for this kind of per-ID conditional filtering. Here's the approach:

We can use the MAX() window function to calculate the highest INDEX value for each ID in the same query, then use that value to decide which rows to keep.

The Query #

SELECT ID, VALUE, INDEX
FROM (
    SELECT 
        ID, 
        VALUE, 
        INDEX,
        MAX(INDEX) OVER (PARTITION BY ID) AS max_index_per_id
    FROM your_table_name
) subquery
WHERE 
    (max_index_per_id >= 2 AND INDEX >= 2)
    OR (max_index_per_id < 2 AND INDEX < 2);

How It Works #

1. Subquery with Window Function: The inner subquery adds a column max_index_per_id that holds the largest INDEX value for each ID. This lets us check, for every row, whether its ID has any rows with INDEX ≥2.
2. Conditional Filter: The outer WHERE clause applies your rule:
   • If the ID's max INDEX is ≥2, keep only rows where INDEX ≥2.
   • If the ID's max INDEX is <2, keep all rows where INDEX <2.

Testing with Your Sample Data #

When you run this against your sample dataset, it will return exactly the result you provided:

• For ID1 and ID2 (both have max INDEX ≥2), only rows with INDEX ≥2 are kept.
• For ID3 (max INDEX is 1, which is <2), all rows with INDEX <2 are returned.
• For ID4 (max INDEX is 2 ≥2), only the row with INDEX=2 is kept.

This approach is efficient because it scans the table only once, unlike running two separate queries and combining results.

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