代码之家 › 专栏 › 技术社区 › Lee Mac

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

ms-access sql

0

Lee Mac · 技术社区 · 7 年前

源表

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 .

我试过的

)使用以下查询:

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 查询。

4 回复 | 直到 6 年前

1

Yogesh Sharma 7 年前

我会用 NOT EXISTS

select category, count(*)
from MyTable t
where not exists (select 1 from MyTable t1 where t1.code = t.code and t1.category <> t.category)
group by category;

2

1

Gordon Linoff 7 年前

可以使用两个聚合级别:

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;

3

0

isaace 7 年前

这也会起作用:

select category, count(*) from(
    select a.category,  b.count from mytable a join (
        select code, count(category) as count 
        from mytable 
        group by code 
        having count(category) = 1
    ) b on b.code = a.code
) c group by category

4

0

Lee Mac 7 年前

从@isaace的回答中,我也想到了这个-

SELECT MyTable.Category, COUNT(*)
FROM 
    MyTable INNER JOIN 
    (SELECT Code FROM MyTable GROUP BY Code HAVING COUNT(Category) = 1) a 
    ON MyTable.Code = a.Code
GROUP BY MyTable.Category