英文:
JOIN of 2 tables where cross all records
问题
根据这两个表格，你可以编写以下查询来获取与MyConditions表中的所有记录匹配的MyValues表中的Ids，其中MyValues.IdValue = MyConditions.IdValue：
SELECT mv.Id
FROM MyValues mv
WHERE mv.IdValue IN (SELECT DISTINCT mc.IdValue FROM MyConditions mc)
GROUP BY mv.Id
HAVING COUNT(DISTINCT mv.IdValue) = (SELECT COUNT(DISTINCT mc.IdValue) FROM MyConditions mc);
查询的结果将是1，因为MyValues表中的Id 1与MyConditions表中的所有记录匹配。
英文:
Given these 2 tables
Table name: MyValues
| Id | IdValue |
|---|---|
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 1 | 11 |
Table name: MyConditions
| Id | IdValue |
|---|---|
| 7 | 10 |
| 7 | 11 |
How could I write a query to get the Ids from the MyValues table to return the ones that match all the records on MyConditions table where MyValues.IdValue = MyConditions.IdValue
So the result would be 1 (since Id 1 from MyValues table matches all records in MyConditions table)
答案1
得分: 2
这似乎是一个关系除法问题。我们可以使用join和having来连接和筛选：
select v.id
from myvalues v
inner join myconditions c on c.idvalue = v.idvalue
group by v.id
having count(*) = (select count(*) from myconditions)
这假设在myvalue中没有重复的(id, idvalue)，并且在mycondition中没有重复的idvalue。否则，我们通常会在having的一侧或两侧使用distinct：
having count(distinct v.idvalue) = (select count(distinct idvalue) from myconditions)
英文:
This reads like a relational division problem. We can join, and filter with having:
select v.id
from myvalues v
inner join myconditions c on c.idvalue = v.idvalue
group by v.id
having count(*) = (select count(*) from myconditions)
This assumes no duplicate (id, idvalue) in myvalue, and no duplicate idvalue in mycondition. Otherwise, we would typically use distinct on one or both side of having:
having count(distinct v.idvalue) = (select count(distinct idvalue) from myconditions)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论