mysql - 按顺序计数

Question

我有一个数据集，其中包含 id 列表，这些 id 是类别列表(因此每个 id 可能有许多类别)。我想搜索类别列表并查看该类别中有多少个不同的 id(计数)，但是一旦 id 被计入一个类别，它就不会被计入另一个类别。

示例:

ID  Category1    Gas Station1    Convenience Store1    Barber2    Day Care2    Gas station3    Convenience Store3    Golf Range

So if I am doing a search of counts on gas station and convenience store (in that order) Gas Station will get a count of 2 (For id 1&2) and then Convenience store will get a count of 1 (id 3).

Currently my query looks like:

select category,distinct(id) from TABLE
where id in ('Gas Station','Convenience Store')
group by category 

它会给我

Gas Station - 2Convenience Store - 2

这不是我想要的。期望的输出:

Gas Station - 2Convenience Store - 1

Answer 1

目前还不清楚为什么需要此输出，但从技术上讲，您可以通过查询生成它

SELECT category, COUNT(DISTINCT id) count
  FROM table1
 WHERE category = 'Gas Station'
UNION ALL
SELECT category, COUNT(DISTINCT id) count
  FROM table1
 WHERE category = 'Convenience Store' 
   AND id NOT IN
       (
         SELECT DISTINCT id
           FROM table1
          WHERE category = 'Gas Station'
       );

输出:

|          CATEGORY | COUNT ||-------------------|-------||       Gas Station |     2 || Convenience Store |     1 |

这里是SQLFiddle 演示