如何修复多表关联SELECT语句实现重复曲目标题检索
问题根源
- 之前用
GROUP BY + HAVING查询返回空结果,核心是分组维度设置错误:如果把trknum、cdtitle这类能唯一标识单条track的字段纳入分组键,由于track表的主键是(cdid, trknum),所有行天然唯一,分组后每组计数永远为1,无法触发HAVING COUNT(*) > 1的重复判定条件。 - 当前无筛选的全量关联查询没有做重复值过滤:一方面把单CD内仅出现1次的曲目(如Stomp of King Porter、Sing a Study in Brown)全部返回;另一方面没有锁定重复判定边界——结合预期输出与实际输出的差异,需要筛选的重复是同一CD内的重复曲目,即单张CD下相同曲目标题对应≥2个不同的trknum,跨CD出现的同名曲目不算重复(否则实际输出里Swing CD上的Cousin Mary(trknum14)也会被纳入结果,与预期不匹配)。
正确实现方案
核心筛选逻辑:先定位到「同CD下、同曲目标题对应的轨道数≥2」的所有组合,再匹配关联的明细字段返回。
方案1:子查询预筛重复集合
先通过分组找到所有符合重复条件的(cdid, comptitle)组合,再关联回各表取明细:
SELECT c.comptitle, t.trknum, cd.cdtitle FROM track t -- 关联基础表 JOIN recording r ON t.rcdid = r.rcdid JOIN composition c ON r.compid = c.compid JOIN cd ON t.cdid = cd.cdid -- 筛选同CD下重复的曲目 WHERE EXISTS ( SELECT 1 FROM track t2 JOIN recording r2 ON t2.rcdid = r2.rcdid JOIN composition c2 ON r2.compid = c2.compid WHERE -- 匹配同CD t2.cdid = t.cdid -- 匹配同曲目标题 c2.comptitle = c.comptitle GROUP BY t2.cdid, c2.comptitle -- 至少存在2条不同的轨道 HAVING COUNT(DISTINCT t2.trknum) > 1 ) ORDER BY c.comptitle, t.trknum;
方案2:窗口函数简化逻辑
对同CD、同曲目标题的分区直接做计数,筛出计数大于1的行,避免重复扫描表:
SELECT comptitle, trknum, cdtitle FROM ( SELECT c.comptitle, t.trknum, cd.cdtitle, -- 计算同CD下同曲目标题的轨道总数 COUNT(*) OVER (PARTITION BY t.cdid, c.comptitle) AS track_cnt FROM track t JOIN recording r ON t.rcdid = r.rcdid JOIN composition c ON r.compid = c.compid JOIN cd ON t.cdid = cd.cdid ) AS base WHERE track_cnt > 1 ORDER BY comptitle, trknum;
结果匹配说明
按照上述逻辑筛选后,返回的结果会完全匹配预期:
- 仅保留Giant Steps CD下,Cousin Mary(trknum2、10)、Giant Steps(trknum1、8)这4条存在同CD重复的记录
- 自动过滤Swing CD下仅出现1次的所有曲目,也不会把跨CD出现的同名Cousin Mary(Swing CD trknum14)纳入重复结果。
内容的提问来源于stack exchange,提问作者CamWhis
相关产品推荐
相关产品推荐

