英文:
Possible to count number of occurrences in a "group" in MySQL?
问题
抱歉，标题可能有误导，我不太清楚我想要实现的术语。但让我们考虑这个表格：
CREATE TABLE entries (
id INT NOT NULL,
number INT NOT NULL
);
假设它包含与每个id关联的四个数字，就像这样：
id number
1 0
1 9
1 17
1 11
2 5
2 8
2 9
2 0
.
.
.
是否可能只使用SQL查询来计算与id关联的任意两个给定数字（元组）的匹配次数？
假设我想计算与唯一id关联的数字0和9的出现次数。在上面的示例数据中，0和9出现了两次（一次在id=1，一次在id=2）。我无法想出如何编写解决此问题的SQL查询。这是否可能？也许我的表结构有问题，但这是我目前数据的组织方式。
我尝试过子查询、联合查询、连接和其他一切，但还没有找到方法。
英文:
Sorry if the title is misleading, I don't really know the terminology for what I want to accomplish. But let's consider this table:
CREATE TABLE entries (
id INT NOT NULL,
number INT NOT NULL
);
Let's say it contains four numbers associated with each id, like this:
id number
1 0
1 9
1 17
1 11
2 5
2 8
2 9
2 0
.
.
.
Is it possible, with a SQL-query only, to count the numbers of matches for any two given numbers (tuples) associated with a id?
Let's say I want to count the number of occurrences of number 0 and 9 that is associated with a unique id. In the sample data above 0 and 9 does occur two times (one time where id=1 and one time where id=2). I can't think of how to write a SQL-query that solves this. Is it possible? Maybe my table structure is wrong, but that's how my data is organized right now.
I have tried sub-queries, unions, joins and everything else, but haven't found a way yet.
答案1
得分: 1
您可以使用GROUP BY和HAVING子句：
SELECT COUNT(s.id)
FROM (
SELECT t.id
FROM YourTable t
WHERE t.number IN (0, 9)
GROUP BY t.id
HAVING COUNT(DISTINCT t.number) = 2
) s
或者使用EXISTS()：
SELECT COUNT(DISTINCT t.id)
FROM YourTable t
WHERE EXISTS (
SELECT 1
FROM YourTable s
WHERE t.id = s.id and s.number IN (0, 9)
HAVING COUNT(DISTINCT s.number) = 2
)
英文:
You can use GROUP BY and HAVING clauses:
SELECT COUNT(s.id)
FROM(
SELECT t.id
FROM YourTable t
WHERE t.number in(0,9)
GROUP BY t.id
HAVING COUNT(distinct t.number) = 2) s
Or with EXISTS():
SELECT COUNT(distinct t.id)
FROM YourTable t
WHERE EXISTS(SELECT 1 FROM YourTable s
WHERE t.id = s.id and s.id IN(0,9)
HAVING COUNT(distinct s.number) = 2)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论