sql - 如何修复 SELECT 语句以返回重复项

Question

目前我正在尝试返回一个三表连接以查找我的“曲目”表中的重复曲目标题，并返回曲目编号 | 其他连接表的 cd 标题。我的 select 语句当前正在返回连接表中的所有信息，但它不只显示重复项。

我也尝试过使用 group by 和 having 子句来尝试查找 COUNT 的 comptitle。当我尝试它返回一个空表时。

我的表：

CREATE TABLE composition (compid, comptitle,...,
    PRIMARY KEY (compid),...);

CREATE TABLE recording (rcdid,..., compid,
    PRIMARY KEY (rcdid, compid),...);

CREATE TABLE cd (cdid, cdtitle,..., 
    PRIMARY KEY(cdid),...);

CREATE TABLE track (cdid, trknum,..., rcdid, compid,
    PRIMARY KEY (cdid, trknum),...);

我的声明：

SELECT comptitle, trknum, cdtitle
    FROM track JOIN recording ON track.rcdid = recording.rcdid
    JOIN composition ON recording.compid = composition.compid
    JOIN cd ON cd.cdid = track.cdid;

预期输出 | 实际的：

EXPECTED:
      comptitle      | trknum | cdtitle     
--------------------------------------------
 Cousin Mary         | 2      | Giant Steps 
 Cousin Mary         | 10     | Giant Steps 
 Giant Steps         | 1      | Giant Steps 
 Giant Steps         | 8      | Giant Steps 

ACTUAL:
         comptitle          | trknum |   cdtitle   
----------------------------+--------+-------------
 Giant Steps                | 8      | Giant Steps
 Giant Steps                | 1      | Giant Steps
 Stomp of King Porter       | 1      | Swing
 Sing a Study in Brown      | 2      | Swing
 Cousin Mary                | 14     | Swing
 Cousin Mary                | 10     | Giant Steps

标签： sqlpostgresql

Answer 1

您需要的是一个子查询，首先在track表中查找重复的曲目标题，然后将其连接到其他表。这个子查询看起来像：

  SELECT rcdid, COUNT(*) AS number
  FROM track
  GROUP BY rcdid
  HAVING COUNT(*) > 1

现在，根据您使用的数据库引擎，您可能可以使用 CTE，使其更具可读性。如果这是您的情况，您可以尝试：

WITH CTE_DuplicatedTracks AS (
  SELECT rcdid, COUNT(*) AS number
  FROM track
  GROUP BY rcdid
  HAVING COUNT(*) > 1
)
SELECT
  c.comptitle,
  t.trknum,
  cd.cdtitle
FROM
  CTE_DuplicatedTracks dt
  JOIN track t ON
    dt.rcdid = t.rcdid
  JOIN recording r ON
    t.rcdid = r.rcdid
  JOIN composition c ON
    r.compid = c.compid
  JOIN cd
    ON cd.cdid = t.cdid;

如果您的引擎不支持 CTE：

SELECT
  c.comptitle,
  t.trknum,
  cd.cdtitle
FROM
  (
    SELECT rcdid, COUNT(*) as number
    FROM track
    GROUP BY rcdid
    HAVING COUNT(*) > 1
  ) dt
  JOIN track t ON
    dt.rcdid = t.rcdid
  JOIN recording r ON
    t.rcdid = r.rcdid
  JOIN composition c ON
    r.compid = c.compid
  JOIN cd
    ON cd.cdid = t.cdid;