mysql - 如何在 MySQL 上构建检索 GROUP BY 统计信息的 SQL 查询

Question

我有两张表，系列和卡片(一对多:每个系列有很多张卡片)

series: seriesID, seriesName...
cards: cardID, seriesID, cardActive (bit), cardUsed(bit)

我想返回以下查询:

• 每个系列共有多少张牌？
• 每个系列有多少张不活动的卡牌？
• 每个系列有多少张二手卡？
• 每个系列有多少张未使用和有效的卡牌？

目标是拥有一个包含以下标题的表格:

seriesID | seriesName | total cards count | inactive cards | etc..

我尝试过使用这个:

SELECT seriesID,COUNT(*) AS cardsCount FROM cards GROUP BY seriesID

但我想知道是否需要 5 个不同的查询才能获取指定的统计信息。我应该使用函数吗？

Answer 1

您可能可以对位字段进行求和(除非生成奇偶校验值而不是整数)，并且 CASE 表达式有助于解决最后一个问题:

 SELECT s.SeriesID,
        s.SeriesName,
        COUNT(*)              AS "Total Cards",
        SUM(NOT c.CardActive) AS "Inactive Cards",
        SUM(NOT c.CardUsed)   AS "Unused Cards",
        SUM(CASE WHEN c.CardActive AND NOT c.CardUsed THEN 1 ELSE 0 END)
                              AS "Active and Unused"
   FROM Series AS s
   JOIN Cards  AS c ON s.SeriesID = c.SeriesID
  GROUP BY s.SeriesID, s.SeriesName;

鉴于它是 MySQL，您可以从 GROUP BY 中省略 s.SeriesName，并且您最终可能会得到相同的答案；大多数其他 DBMS 都需要 GROUP BY 子句中的两个非聚合列。

SUM(NOT c.CardActive) 和 SUMN(NOT c.CardUsed) 表达式可能无法达到我想要的效果。在这种情况下:

       SUM(CASE WHEN c.CardActive THEN 0 ELSE 1 END) AS "Inactive Cards",
       SUM(CASE WHEN c.CardUsed   THEN 0 ELSE 1 END) AS "Unused Cards",

就可以了。

对于至少列出一张卡的系列来说，这一切都是干净的。如果您的系列中没有卡片并且您希望它们出现在输出中，那么您需要在表之间使用 LEFT OUTER JOIN，并且您必须担心空值。这次，整个过程中都需要 SUM(CASE...END)。请注意，COUNT(C.CardID) 仅计算非空值，因此当系列没有卡片时，这将返回零。

 SELECT s.SeriesID,
        s.SeriesName,
        COUNT(c.CardID) AS "Total Cards",
        SUM(CASE WHEN c.CardID IS NULL THEN 0 WHEN c.CardActive THEN 0 ELSE 1 END)
                        AS "Inactive Cards",
        SUM(CASE WHEN c.CardID IS NULL THEN 0 WHEN c.CardUsed   THEN 0 ELSE 1 END)
                        AS "Unused Cards",
        SUM(CASE WHEN c.CardID IS NULL THEN 0
                 WHEN c.CardActive AND NOT c.CardUsed THEN 1 ELSE 0
            END)        AS "Active and Unused"
   FROM Series AS s
   LEFT JOIN Cards AS c ON s.SeriesID = c.SeriesID
  GROUP BY s.SeriesID, s.SeriesName;