sql - 计数具有唯一字段值的记录

Question

源表

假设我有一个名为MyTable内容的表：

+----------+------+
| Category | Code |
+----------+------+
|    A     | A123 |
|    A     | B123 |
|    A     | C123 |
|    B     | A123 |
|    B     | B123 |
|    B     | D123 |
|    C     | A123 |
|    C     | E123 |
|    C     | F123 |
+----------+------+

我正在尝试计算Code每个类别独有的值的数量。

---

期望的结果

对于上面的示例，结果将是：

+----------+-------------+
| Category | UniqueCodes |
+----------+-------------+
|    A     |           1 |
|    B     |           1 |
|    C     |           2 |
+----------+-------------+

因为C123是 独有的A,D123是 独有的B, E123&F123是 独有的C.

---

我试过的

我可以C使用以下查询获得单个类别（例如）的结果：

SELECT COUNT(a.Code) AS UniqueCodes
FROM 
    (
        SELECT MyTable.Code
        FROM MyTable
        WHERE MyTable.Category = "C"
    ) a
    LEFT JOIN
    (
        SELECT MyTable.Code
        FROM MyTable
        WHERE MyTable.Category <> "C"
    ) b
    ON a.Code = b.Code
WHERE b.Code IS NULL

然而，虽然我可以为每个类别硬编码一个查询，但我似乎无法构造一个查询来计算每个可能的Category值。

这是我尝试过的：

SELECT c.Category, 
    (
        SELECT COUNT(a.Code) 
        FROM 
            (
                SELECT MyTable.Code 
                FROM MyTable 
                WHERE MyTable.Category = c.Category
            ) a 
            LEFT JOIN
            (
                SELECT MyTable.Code
                FROM MyTable
                WHERE MyTable.Category <> c.Category
            ) b 
            ON a.Code = b.Code 
        WHERE b.Code IS NULL
    ) AS UniqueCodes
FROM
    (
        SELECT MyTable.Category
        FROM MyTable
        GROUP BY MyTable.Category
    ) c

但是，没有在嵌套查询c.Category的范围内定义。SELECT

谁能建议我如何获得预期的结果？

标签： sqlms-access

Answer 1

您可以使用两个级别的聚合：

select minc as category, count(*)
from (select code, min(category) as minc, max(category) as maxc
      from t
      group by code
     ) as c
where minc = maxc
group by minc;