Oracle中两表关联并基于日期条件生成分类标签（1/0）的实现方案求助

Got it, let's work through this. You need to add a binary label to your Table1 where the label is 1 if the corresponding ID in Table2 has a date in August 2021 (21/08/01 to 21/08/31), and 0 otherwise. Here are two solid approaches for Oracle SQL:

Solution 1: Using EXISTS (Recommended for Performance) #

This is my go-to because EXISTS stops searching as soon as it finds a matching row in Table2, which is efficient especially if Table2 is large.

SELECT 
    t1.ID,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Table2 t2 
            WHERE t2.ID = t1.ID 
              AND t2.Date BETWEEN TO_DATE('21/08/01', 'YY/MM/DD') 
                              AND TO_DATE('21/08/31', 'YY/MM/DD')
        ) THEN 1 
        ELSE 0 
    END AS Label,
    t1."Other data"  -- Include all your other columns from Table1 here
FROM Table1 t1;

Breakdown:

• The EXISTS subquery checks if there's any row in Table2 with the same ID as Table1, and a date within your target range.
• TO_DATE converts your string date literals to Oracle's DATE type (adjust the format mask if your date strings use a different pattern, like 'YYYY/MM/DD').
• The CASE statement maps the existence check to your 1/0 label.

Solution 2: Using LEFT JOIN + Aggregation #

If you prefer joining tables (or need to handle cases where an ID has multiple entries in Table2), this works too. We use MAX to ensure we get 1 even if only one of the matching dates falls in the range.

SELECT 
    t1.ID,
    MAX(
        CASE 
            WHEN t2.Date BETWEEN TO_DATE('21/08/01', 'YY/MM/DD') 
                             AND TO_DATE('21/08/31', 'YY/MM/DD') 
            THEN 1 
            ELSE 0 
        END
    ) AS Label,
    t1."Other data"
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1."Other data";  -- Group by all non-aggregated columns from Table1

Notes:

• If your Table2's Date column is stored as a string (not recommended!), you'll need to convert it to a date first in the condition: TO_DATE(t2.Date, 'YY/MM/DD') BETWEEN ....
• Make sure to include all your "Other data" columns in both the SELECT and GROUP BY clauses in Solution 2.

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