按观影时所属团队统计总观影时长的SQL查询方案咨询

首先，咱们先拆解下你最初的查询问题：你一开始用的LEFT JOIN之后加了WHERE t2.team IS NOT NULL，这就把那些找不到对应团队的观影记录直接过滤掉了——这也是你说它“不工作”的核心原因，因为你本来想统计所有时长，包括无团队映射的部分。

你后来想到的用COALESCE的方案其实已经是最简单直接的解决办法了，完全不需要用CTE来复杂化问题，咱们来拆解下这个方案为什么有效：

SELECT 
  COALESCE(t2.team, 'UNMAPPED') AS team,
  SUM(t1.hours_watched) AS total_hours
FROM myt1 t1
LEFT JOIN myt2 t2 
  ON t1.name = t2.name 
  AND t1.date >= t2.date_from 
  AND t1.date <= t2.date_to
GROUP BY COALESCE(t2.team, 'UNMAPPED');

这个方案的核心优势： #

• 保留所有观影记录：LEFT JOIN确保myt1里的每一条观影记录都被纳入统计，哪怕找不到对应的团队信息
• 统一无归属记录的分类：COALESCE(t2.team, 'UNMAPPED')把所有找不到团队的记录归到一个统一的“UNMAPPED”分组里，不会丢失这部分时长数据
• 逻辑精准匹配归属：连接条件里的日期判断已经精准把观影时长匹配到了用户当时所在的团队，完全符合你“按观影时的团队归属统计”的需求

那什么时候需要用到CTE？ #

只有当你需要对“有团队映射”和“无团队映射”的记录做更复杂的预处理（比如先分别统计再做额外计算）时，CTE才会发挥作用。比如下面这个CTE版本的实现：

WITH mapped_hours AS (
  SELECT 
    t2.team,
    SUM(t1.hours_watched) AS total_hours
  FROM myt1 t1
  JOIN myt2 t2 
    ON t1.name = t2.name 
    AND t1.date >= t2.date_from 
    AND t1.date <= t2.date_to
  GROUP BY t2.team
),
unmapped_hours AS (
  SELECT 
    'UNMAPPED' AS team,
    SUM(t1.hours_watched) AS total_hours
  FROM myt1 t1
  WHERE NOT EXISTS (
    SELECT 1 
    FROM myt2 t2 
    WHERE t1.name = t2.name 
      AND t1.date >= t2.date_from 
      AND t1.date <= t2.date_to
  )
)
SELECT * FROM mapped_hours
UNION ALL
SELECT * FROM unmapped_hours;

但这个方案和你用COALESCE的方案结果完全一致，却多写了很多冗余代码，属于典型的“过度设计”——你的原始简化方案已经足够清晰高效了。

额外验证提示： #

拿你的测试数据举例：

• name4在2024-12-01的4小时观影记录，因为myt2里没有他的团队信息，会被归到UNMAPPED
• name3在2024-07-15的5.5小时记录，因为他的团队截止到2024-06-30，也会被归到UNMAPPED
• 其他所有记录都会精准匹配到当时的团队，最终统计结果完全符合你的需求

总结下：你自己想到的COALESCE+LEFT JOIN的方案就是最优解，不需要用CTE，逻辑清晰、代码简洁，还能完整保留所有数据。

内容来源于stack exchange