如何查询多张卡组中重复角色卡牌的存在及数量（不修改底层数据）

首先得指出原表结构里的几个小问题，不然没法完成正确的关联查询：

• character表的主键写错了，应该是characterId而不是userId
• card表的定义没写完，我假设它包含characterId字段来关联对应的角色；另外，卡组和卡牌是典型的多对多关系，所以肯定还需要一张中间表（比如deck_card）来记录每个卡组包含的卡牌，我先补出这张表的结构：

  CREATE TABLE `deck_card` (
    `deckId` BIGINT(20) NOT NULL,
    `cardId` BIGINT(20) NOT NULL,
    PRIMARY KEY (`deckId`, `cardId`),
    FOREIGN KEY (`deckId`) REFERENCES `deck`(`deckId`),
    FOREIGN KEY (`cardId`) REFERENCES `card`(`cardId`)
  );
  

假设这些关联关系都补全了，下面给你两种查询方案，按需选择：

方案一：统计每个角色的卡牌覆盖的卡组数量及总出现次数 #

这个方案会给出每个角色的卡牌被多少个不同卡组包含，以及这些卡牌在所有卡组里的总实例数：

SELECT
    c.name AS character_name,
    COUNT(DISTINCT dc.deckId) AS deck_count,
    COUNT(dc.cardId) AS total_card_instances
FROM
    `character` c
JOIN
    `card` cd ON c.characterId = cd.characterId
JOIN
    `deck_card` dc ON cd.cardId = dc.cardId
GROUP BY
    c.characterId, c.name
HAVING
    COUNT(DISTINCT dc.deckId) > 1; -- 只保留存在于多个卡组的角色

关键逻辑说明： #

• COUNT(DISTINCT dc.deckId)：统计该角色的卡牌涉及的不同卡组数量（去重，避免同一卡组被重复统计）
• COUNT(dc.cardId)：统计该角色的卡牌在所有卡组里的总出现次数（包括同一卡组里多次出现同一张卡牌的情况）
• HAVING子句：过滤掉只出现在单个卡组的角色，如果你想看到所有角色的情况，直接去掉这一行就行

方案二：细化到单张卡牌的卡组分布 #

如果需要更详细的信息，比如每张属于该角色的卡牌分别在哪些卡组里，用这个查询：

SELECT
    c.name AS character_name,
    cd.cardId,
    cd.color, -- 保留card表的color字段
    GROUP_CONCAT(DISTINCT d.title SEPARATOR ', ') AS deck_titles,
    COUNT(DISTINCT d.deckId) AS deck_count
FROM
    `character` c
JOIN
    `card` cd ON c.characterId = cd.characterId
JOIN
    `deck_card` dc ON cd.cardId = dc.cardId
JOIN
    `deck` d ON dc.deckId = d.deckId
GROUP BY
    c.characterId, c.name, cd.cardId, cd.color
HAVING
    COUNT(DISTINCT d.deckId) > 1;

关键逻辑说明： #

• GROUP_CONCAT会把该卡牌所在的所有卡组标题拼接成一个字符串，方便直观查看
• 这个结果会按角色+卡牌分组，能看到每张跨卡组的卡牌具体分布

额外提示 #

如果你的实际表关联逻辑和我假设的不一样（比如deck表直接存储卡牌ID列表？这其实不符合数据库设计规范），需要根据实际的关联关系调整JOIN部分的逻辑。

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