按顺序计数 [英] Count in sequential order

问题描述

我有一个数据集，有一个id列表，这些id是一个类别列表（所以每个id可能有很多类别）。我想搜索类别列表，并查看该类别（计数）中有多少不同的ID，但一旦ID计入一个类别，则不会计入另一个类别。

I have a data set that has a list of id's and with those id's are a list of categories (so each id may have many categories). I want to search a list of categories and see how many different id's are within that category (count) but once a id is counted in one category it will not be counted in another.

示例：

ID  Category
1    Gas Station
1    Convenience Store
1    Barber
2    Day Care
2    Gas station
3    Convenience Store
3    Golf Range

如果我在加油站和便利店（按顺序）搜索计数加油站将得到2的计数（对于id 1& 2），然后便利店将得到1（id 3）的计数。

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).

目前我的查询类似：

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

它会给我

Gas Station - 2
Convenience Store - 2

这不是我想要的。所需输出：

And it's not what I want. Desired output:

Gas Station - 2
Convenience Store - 1

推荐答案

更新

/ p>

Try this as a single SQL:

SELECT Category
 ,COUNT(*)
 ,@ids:=CONCAT( @ids, ID, ',' )
 FROM Table1, (SELECT @ids:=',') ids
 WHERE Category IN ('Gas Station','Convenience Store')
 AND POSITION( CONCAT( ',', ID, ',' ) IN @ids ) = 0
 GROUP BY Category

SQLfiddle在 http://sqlfiddle.com/#!2/2f026/12

SQLfiddle at http://sqlfiddle.com/#!2/2f026/12

已更新：

请尝试：

SET @ids:=',';
SELECT Category
      ,COUNT(*)
      ,@ids:=CONCAT( @ids, ID, ',' )
  FROM Table1
 WHERE Category IN ('Gas Station','Convenience Store')
   AND POSITION( CONCAT( ',', ID, ',' ) IN @ids ) = 0
 GROUP BY Category

修改@ peterm的sqlfiddle（ http://sqlfiddle.com/#!2/2f026/1 ）结果是：

Modifying @peterm's sqlfiddle (http://sqlfiddle.com/#!2/2f026/1) the results are:

CATEGORY             COUNT(*)   @IDS:=CONCAT( @IDS, ID, ',' )
Convenience Store    1          ,1,3,
Gas Station          2          ,1,

这篇关于按顺序计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！