SQL多表合并去重：按指定条件生成无重复结果表需求

需求说明 #

• 针对每个t1name，遍历所有t2date：若存在t2update=1的记录则选用该记录，无1则选用t2update=0的记录
• 若t1name未出现在t2name中，则以t1date创建记录并将t2update设为0

表结构示例 #

Table 1 #

| t1name | t1date     | t1department |
| ------ | ---------- | ------------ |
| name 1 | 2000.01.01 | tlc          | 
| name 1 | 2000.01.01 | tlc          |
| name 2 | 2000.01.04 | non-tlc      |
| name 3 | 2000.01.04 | non-tlc      |
| name 4 | 2000.01.04 | tlc          |
| name 5 | 2000.01.04 | tlc          |
| name 6 | 2000.01.04 | tlc          |
| name 7 | 2000.01.04 | tlc          |  

Table 2 #

| t2name | t2update | t2date       |
| ------ | -------- | ------------ |
| name 1 | 1        | 2000.01.01   |
| name 1 | 0        | 2000.01.02   | 
| name 1 | 1        | 2000.01.02   | 
| name 2 | 1        | 2000.01.04   | 
| name 2 | 0        | 2000.01.04   | 
| name 2 | 0        | 2000.01.09   | 
| name 3 | 0        | 2000.01.09   | 
| name 3 | 1        | 2000.01.05   | 
| name 4 | 0        | 2000.01.03   |

预期结果表 #

| rname  | rupdate | rdate        |
| ------ | ------- | ------------ |
| name 1 | 1       | 2000.01.01   |
| name 1 | 1       | 2000.01.02   | 
| name 2 | 1       | 2000.01.04   | 
| name 3 | 0       | 2000.01.02   | 
| name 3 | 1       | 2000.01.05   | 
| name 4 | 0       | 2000.01.03   | 
| name 5 | 0       | 2000.01.09   | 
| name 6 | 0       | 2000.01.09   | 
| name 7 | 0       | 2000.01.09   | 

当前使用的SQL语句（存在问题） #

CREATE OR REPLACE VIEW "rtable" AS 
(
   SELECT DISTINCT
      ((CASE WHEN (t2.t2updates) > 0) AND (MAX(t1.t1date))) THEN name
    , t1.date
    , t1.t1department
    , t2.updates 
FROM (table1 t1
LEFT JOIN table2 t2 on (t2.t2name = t1.t1name))
GROUP BY 
    , t1.date
    , t1.t1department
    , t2.updates
ORDER BY t1.t1name ASC
)

问题点 #

当前结果存在每日重复记录，同一t1name和日期下同时出现t2update=1和0的条目，不符合需求中“优先选1，无1则选0”的规则。

修正后的SQL语句 #

CREATE OR REPLACE VIEW "rtable" AS
WITH cleaned_t2 AS (
    -- 对t2按name和日期分组，优先保留update=1的记录
    SELECT 
        t2name AS rname,
        MAX(t2update) AS rupdate,
        t2date AS rdate
    FROM table2
    GROUP BY t2name, t2date
),
unique_t1 AS (
    -- 去重t1中的name，避免重复生成缺失记录
    SELECT DISTINCT
        t1name,
        t1date
    FROM table1
),
t1_missing_in_t2 AS (
    -- 处理t1中存在但t2中没有的name，生成update=0的记录
    SELECT
        ut.t1name AS rname,
        0 AS rupdate,
        ut.t1date AS rdate
    FROM unique_t1 ut
    LEFT JOIN cleaned_t2 ct ON ut.t1name = ct.rname
    WHERE ct.rname IS NULL
)
-- 合并两部分结果并排序
SELECT rname, rupdate, rdate FROM cleaned_t2
UNION ALL
SELECT rname, rupdate, rdate FROM t1_missing_in_t2
ORDER BY rname, rdate;

逻辑说明 #

1. cleaned_t2：对Table2按t2name和t2date分组，用MAX(t2update)确保同一日期下优先取1（1>0），自动过滤同一name+date下的重复0记录。
2. unique_t1：对Table1去重，避免同一name生成多条重复的缺失记录。
3. t1_missing_in_t2：找出Table1中存在但Table2中没有的name，生成t2update=0的记录，用t1date作为日期。
4. UNION ALL：合并清洗后的Table2数据和t1缺失数据，最后按name和日期排序。

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