SQL-通過聯結表連接表時如何避免重復行？

我正在構建一個 Pokemon API，并且在通過聯結表加入時遇到重復行的問題。它是一個 SQL Server 資料庫。我的表的淡化版本如下所示：

要重新創建我的資料庫：

CREATE DATABASE PokeApi;
USE PokeApi;

CREATE TABLE Pokemon (
    PokemonId INT PRIMARY KEY IDENTITY,
    PokemonName VARCHAR(55),
    NationalDexNumber INT,
    PokemonHeight INT,
    PokemonWeight DECIMAL(10,2),
    Category VARCHAR(50),
    Gender VARCHAR(15),
    GenderDifference BIT,
    TypeOne VARCHAR(15),
    TypeTwo VARCHAR(15),
);

CREATE TABLE Abilities (
    AbilityId INT PRIMARY KEY IDENTITY,
    AbilityName VARCHAR(20)
);

CREATE TABLE PokemonAbilitiesMapped (
    PokemonId INT NOT NULL,
    AbilityId INT,
    CONSTRAINT PK_PokemonAbilitiesMapped PRIMARY KEY
    (
        PokemonId,
        AbilityId
    ),
    FOREIGN KEY(PokemonId) REFERENCES Pokemon(PokemonId),
    FOREIGN KEY(AbilityId) REFERENCES Abilities(AbilityId)
);

INSERT INTO Pokemon
VALUES
  ('Venusaur', 3, 79, 220.5, 'Seed', 'Male / Female', 1, 'Grass', 'Poison'),
  ('Mega Venusaur', 3, 94, 342.8, 'Seed', 'Male / Female', 0, 'Grass', 'Poison'),
  ('Gigantamax Venusaur', 3, 945, NULL, 'Seed', 'Male / Female', 1, 'Grass', 'Poison');

INSERT INTO Abilities
VALUES
  ('Overgrow'),
  ('Thick Fat'),
  ('Test');

INSERT INTO PokemonAbilitiesMapped
VALUES
  (1, 1),
  (2, 2),
  (3, 1),
  (3, 3);

我的查詢：

SELECT p.PokemonName, p.NationalDexNumber, p.PokemonHeight, p.PokemonWeight,
p.Category, p.Gender, p.GenderDifference, p.TypeOne, p.TypeTwo,
a.AbilityName
FROM Pokemon p
JOIN PokemonAbilitiesMapped pam
ON p.PokemonId = pam.PokemonId
JOIN Abilities a
ON pam.AbilityId = a.AbilityId

輸出這個：

如您所見，Gigantamax Venusaur被列出了兩次，因為它有兩種能力。我在想我可以添加另一個名為“AbilityNameTwo”的列，只有當被查詢的口袋妖怪有第二個能力來避免行重復時。

What is the best way to avoid repeating data in this scenario? If I should add another column when joining, how would I write that as a query?

uj5u.com熱心網友回復：

加入資料時出現這樣的結果是完全正常的。如果你真的想，你可以有“能力二”，但如果某些口袋妖怪有三個或五個呢？你如何訂購它們？這是一個示例，說明您如何為兩種能力執行此操作并按字母順序排列：

WITH abilitiesMapped AS
(
    SELECT pam.PokemonId
         , a.AbilityName
         , ROW_NUMBER() OVER (PARTITION BY pam.PokemonId 
                                  ORDER BY a.AbilityName) AS abilityNo
      FROM PokemonAbilitiesMapped pam
      JOIN Abilities a
        ON a.AbilityId = pam.AbilityId
)
SELECT p.PokemonName, p.NationalDexNumber, p.PokemonHeight, p.PokemonWeight,
p.Category, p.Gender, p.GenderDifference, p.TypeOne, p.TypeTwo
   , ability1.abilityName AS ability1
   , ability2.abilityName AS ability2
FROM Pokemon p
LEFT OUTER
JOIN abilitiesMapped ability1
  ON ability1.PokemonId = p.PokemonId
 AND ability1.abilityNo = 1
LEFT OUTER
JOIN abilitiesMapped ability2
  ON ability2.PokemonId = p.PokemonId
 AND ability1.abilityNo = 2

CTEabilitiesMapped 獲取您的映射和能力，并為每個口袋妖怪生成一個能力編號 ( PARTITION BY pam.PokemonId)，其中數字按能力名稱 ( ORDER BY ...) 排序。然后這個 CTE 被加入了兩次能力 1 和 2。LEFT JOIN這很重要，因為口袋妖怪不一定需要有兩個能力，在這種情況下你會在能力 2 中得到一個 NULL（你只會得到有兩個能力INNER JOIN的那些）能力，因為abilityNo = 2 需要存在才能滿足加入條件）。

uj5u.com熱心網友回復：

感謝您使用創建、插入陳述句的帖子。這很有幫助。

為避免技能名稱重復，您可以將技能名稱設為逗號分隔串列。我STR_AGG用來生成逗號分隔的串列。它從 SQL Server 2017 開始提供。對于早期版本，您必須使用FOR XML PATH生成逗號分隔串列。

SELECT p.PokemonName, p.NationalDexNumber, p.PokemonHeight, p.PokemonWeight,
p.Category, p.Gender, p.GenderDifference, p.TypeOne, p.TypeTwo,
STRING_AGG(a.AbilityName,',') AS Abilities
FROM Pokemon p
JOIN PokemonAbilitiesMapped pam
ON p.PokemonId = pam.PokemonId
JOIN Abilities a
ON pam.AbilityId = a.AbilityId
GROUP BY p.PokemonName, p.NationalDexNumber, p.PokemonHeight, p.PokemonWeight,
p.Category, p.Gender, p.GenderDifference, p.TypeOne, p.TypeTwo

標籤：sql sql-server tsql join many-to-many

上一篇：在MySQL查詢中按選定日期和倉庫顯示庫存數量的INWARD和OUTWARD

下一篇：如何根據R中的值合并資料框列