这就是我想到的：

SELECT unique_id
    ,count(unique_id)
FROM (
    SELECT a.id AS locationA
        ,b.id AS locationB
        ,CASE WHEN locationA IS NULL THEN 'onlyb' WHEN locationB IS NULL THEN 'onlya' ELSE 'both' END AS unique_id
    FROM (
        SELECT DISTINCT id
            ,location
        FROM table
        WHERE 1 = 1
            AND location = 'A'
        ) a
    FULL JOIN (
        SELECT DISTINCT id
            ,location
        FROM table
        WHERE 1 = 1
            AND location = 'B'
        ) b ON a.id = b.id
    )
GROUP BY id

我们可以尝试以下查询来查找 id 有两个位置：

SELECT id
FROM yourTable
WHERE location IN ('A', 'B')
GROUP BY id
HAVING MIN(location) <> MAX(location);

找到那些 id 只有位置的 A :

SELECT id
FROM yourTable
GROUP BY id
HAVING MIN(location) = MAX(location) AND MIN(location) = 'A';

上面的查询是这样写的，以便我们可以在上面放置索引 (id, location) . mysql应该能够利用这个索引进行两个查询。

获取两个位置的ID

select id
from your_table
where location in ('A','B')
group by id
having count(distinct location) = 2

获取唯一ID

select id
from your_table
group by id
having count(distinct location) = 1