无需INTERSECT的SQL查询：获取L_ID对应的共同R_ID

Got it, let's break down how to solve this without relying on INTERSECT. The core idea is to use grouping and counting to identify which R_ID is linked to every L_ID in your input set.

How It Works #

We need an R_ID that has a matching record for every L_ID in your input collection. Here's the step-by-step logic:

• First, filter all records where L_ID falls into your target set.
• Group the filtered results by R_ID.
• Count how many distinct L_IDs are associated with each R_ID. If this count equals the total number of L_IDs in your input, that's your common R_ID.

Generic Query Template #

Replace your_table_name with your actual table name, update the IN clause with your L_ID set, and set the count value to match the number of L_IDs you're inputting:

SELECT R_ID
FROM your_table_name
WHERE L_ID IN (/* Your L_ID collection here */)
GROUP BY R_ID
HAVING COUNT(DISTINCT L_ID) = /* Number of L_IDs in your input */;

Example 1: Input L_ID (1,2,3) #

Applying the template, the query becomes:

SELECT R_ID
FROM your_table_name
WHERE L_ID IN (1, 2, 3)
GROUP BY R_ID
HAVING COUNT(DISTINCT L_ID) = 3;

This returns R_ID=1 because only R_ID 1 is linked to all three L_IDs (the count of distinct L_IDs for R_ID 1 is exactly 3, matching the size of our input set).

Example 2: Input L_ID (2,3,4,5) #

The corresponding query is:

SELECT R_ID
FROM your_table_name
WHERE L_ID IN (2, 3, 4, 5)
GROUP BY R_ID
HAVING COUNT(DISTINCT L_ID) = 4;

Here, R_ID 3 is the only one linked to all four input L_IDs, so it's returned as expected.

Notes #

• We use COUNT(DISTINCT L_ID) to account for any potential duplicate records (e.g., if the same L_ID-R_ID pair appears multiple times in the table). If you're certain there are no duplicates, you can use COUNT(*) instead, but DISTINCT makes the query more robust.
• Since you mentioned each L_ID combination maps to exactly one common R_ID, this query will always return a single result row, which aligns with your requirements.

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