盘点记录问题
盘点记录问题
提问于 2013-02-21 01:43:12
这可能是一个简单的问题，但我无法理解它。
我有一个MemberBusinessCats表，其中包含一个BusinessCatID和一个MemberID。该表可以这样表示：
+-----------------------+-----------------+------------+
| MemberBusinessCatID | BusinessCatID | MemberID |
+-----------------------+-----------------+------------+
| 27 | 45 | 102 |
+-----------------------+-----------------+------------+
| 28 | 55 | 102 |
+-----------------------+-----------------+------------+
| 29 | 61 | 102 |
+-----------------------+-----------------+------------+
| 30 | 45 | 33 |
+-----------------------+-----------------+------------+
| 31 | 23 | 33 |
+-----------------------+-----------------+------------+
| 32 | 45 | 73 |
+-----------------------+-----------------+------------+
| 32 | 61 | 73 |
+-----------------------+-----------------+------------+
| 32 | 45 | 73 |
+-----------------------+-----------------+------------+如何编写脚本来显示以下数据
+-----------------+---------------------+
| BusinessCatID | NumMembers In Cat |
+-----------------+---------------------+
| 45 | 3 |
+-----------------+---------------------+
| 55 | 1 |
+-----------------+---------------------+
| 61 | 2 |
+-----------------+---------------------+
| 23 | 1 |
+-----------------+---------------------+在此之前，非常感谢您。
neojakey
回答 7
Stack Overflow用户
回答已采纳
发布于 2013-02-21 01:45:26
尝尝这个
select BusinessCatID ,count(BusinessCatID) as NumMembers_In_Cat
from MemberBusinessCats
group by BusinessCatIDStack Overflow用户
发布于 2013-02-21 01:45:34
需要将聚合函数与GROUP BY一起使用
select BusinessCatID, count(*) NumMembersInCat
from MemberBusinessCats
group by BusinessCatID请参阅SQL Fiddle with Demo
这也可以使用count() over()编写
select distinct BusinessCatID,
count(*) over(partition by BusinessCatID) NumMembersInCat
from MemberBusinessCats请参阅SQL Fiddle with Demo
如果要计算每个类别中的成员数量，则可以使用：
select BusinessCatID,
count(distinct MemberID) NumMembersInCat
from MemberBusinessCats
group by BusinessCatID请参阅SQL Fiddle with Demo
Stack Overflow用户
发布于 2013-02-21 01:58:34
根据列出两次但只计算一次的BusinessCatID & MemberID (45,73)的组合，您需要进行计数(DISTINCT x)
SELECT BusinessCatID, COUNT(DISTINCT MemberID) as NumMembersInCat
FROM MemberBusinessCatID
GROUP BY BusinessCatID这将计算每个BusinessCatID的不同成员(基于MemberID)。如果你不担心dups，那么使用COUNT(MemberID)甚至COUNT(1)就可以了。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14986457
复制相关文章
相似问题
腾讯云开发者
