Oracle存储过程SQL优化：优先取CALC1记录，无则查CALC2

Hey there! It sounds like you need a way to prioritize records matching CALC1 and only return CALC2 when no CALC1 exists for a given ordet_id. Let's break down two reliable approaches to fix your query:

Approach 1: Use ROW_NUMBER() Window Function (Recommended) #

This method assigns a priority rank to each matching record, then filters to keep only the highest-priority entry per ordet_id.

SELECT *
FROM (
    SELECT 
        data1.*, data2.*, data3.*, data4.*, data5.*, data6.*, data7.*, data8.*,
        ROW_NUMBER() OVER (
            PARTITION BY data1.ordet_id 
            ORDER BY CASE WHEN data8.code = 'CALC1' THEN 1 ELSE 2 END
        ) AS rn
    FROM database1 data1 
    JOIN database2 data2 ON data2.id = data1.attr_id 
    JOIN database3 data3 ON data3.attr_id = data2.id 
    JOIN database4 data4 ON data4.objt_attr_id = data3.id 
    JOIN database5 data5 ON data5.stya_id = data4.id AND data5.value = 1 
    JOIN database6 data6 ON data6.id = data5.sero_id 
    JOIN database7 data7 ON data7.id = data6.srv_id  -- Fixed alias typo from `srv.id` to `data7.id` (matches your table alias)
    JOIN database8 data8 ON data8.code IN ('CALC1','CALC2') 
    WHERE data1.ordet_id = data8.id  -- Changed IN to = for direct match; adjust if data8.id has multiple values per ordet_id
) ranked_data
WHERE rn = 1;

How this works: #

• The ROW_NUMBER() function groups results by data1.ordet_id, so we handle each order entry separately.
• We order using a CASE statement that gives CALC1 a rank of 1 (higher priority) and CALC2 a rank of 2.
• The outer query filters to keep only the first record (rn = 1) for each ordet_id, ensuring we get the highest-priority match every time.

Approach 2: Use UNION ALL with NOT EXISTS #

This method first fetches all CALC1 matches, then adds CALC2 matches only where no CALC1 exists for the same ordet_id.

-- First get all CALC1 matches
SELECT data1.*, data2.*, data3.*, data4.*, data5.*, data6.*, data7.*, data8.*
FROM database1 data1 
JOIN database2 data2 ON data2.id = data1.attr_id 
JOIN database3 data3 ON data3.attr_id = data2.id 
JOIN database4 data4 ON data4.objt_attr_id = data3.id 
JOIN database5 data5 ON data5.stya_id = data4.id AND data5.value = 1 
JOIN database6 data6 ON data6.id = data5.sero_id 
JOIN database7 data7 ON data7.id = data6.srv_id 
JOIN database8 data8 ON data8.code = 'CALC1' 
WHERE data1.ordet_id = data8.id

UNION ALL

-- Then get CALC2 matches only if no CALC1 exists for the ordet_id
SELECT data1.*, data2.*, data3.*, data4.*, data5.*, data6.*, data7.*, data8.*
FROM database1 data1 
JOIN database2 data2 ON data2.id = data1.attr_id 
JOIN database3 data3 ON data3.attr_id = data2.id 
JOIN database4 data4 ON data4.objt_attr_id = data3.id 
JOIN database5 data5 ON data5.stya_id = data4.id AND data5.value = 1 
JOIN database6 data6 ON data6.id = data5.sero_id 
JOIN database7 data7 ON data7.id = data6.srv_id 
JOIN database8 data8 ON data8.code = 'CALC2' 
WHERE data1.ordet_id = data8.id
AND NOT EXISTS (
    SELECT 1
    FROM database1 d1
    JOIN database8 d8 ON d1.ordet_id = d8.id AND d8.code = 'CALC1'
    WHERE d1.ordet_id = data1.ordet_id
);

How this works: #

• The first part of the UNION ALL pulls all records matching CALC1.
• The second part pulls CALC2 records, but uses NOT EXISTS to exclude any ordet_id that already has a CALC1 match.
• This guarantees you never get duplicate records for the same ordet_id, and CALC1 always takes precedence.

Quick Note: #

I fixed a potential typo in your original query: JOIN database7 data7 ON srv.id = data6.srv_id — since you aliased database7 as data7, I adjusted that to data7.id = data6.srv_id in the examples. If srv was an intentional alias for another table, just swap it back!

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