加入所有值都存在的表的 SQL 有效方法 [英] SQL efficient way to join a table where all values exist
问题描述
table 1
item group_id
123 aaaa
432 bbbb
534 aaaa
765 cccc
656 aaaa
656 cccc
111 cccc
group_id group_name
aaaa groupA
bbbb groupB
cccc groupC
我想找到同时包含 765 和 656 的所有组 - 在本例中 GroupC 包含这两个值.
I want to find all groups that contain both 765 and 656 - in this instance GroupC contains both of those values.
有没有办法避免一堆嵌套查询或 AND 语句?假设表 1 可以有数千个配对,我必须找到包含 30 个项目的所有列表的组编写一个非常大的查询来回答问题会很丑陋.
Is there a way that avoids a bunch of nested queries or AND statements? Say if table 1 can have thousands of pairings and I have to find the groups that contain all of a list of 30 items It would be ugly to write a really big query to answer the question.
我希望我可以说在 (765,656) 中的位置",但让它的行为有所不同.如果有一堆要匹配的项目,它会变得混乱.
I wish I could say "where in (765,656)" but have it behave differently. If there was a bunch of items to match on it would get messy.
SQL Fiddle http://sqlfiddle.com/#!9/6581eb/9一个>
SQL Fiddle http://sqlfiddle.com/#!9/6581eb/9
示例数据(以防 SQL Fiddle 由于某种原因无法访问):
Sample data (in case SQL Fiddle is inaccessible for some reason):
create table table1(itemID int,groupID varchar(4));
insert into table1(itemID,groupID)
values
(123,'aaaa')
,(432,'bbbb')
,(534,'aaaa')
,(765,'cccc')
,(656,'aaaa')
,(656,'cccc')
,(111,'cccc');
create table table2(groupID varchar(4),groupName varchar(6));
insert into table2(groupID,groupName)
values
('aaaa','groupA')
,('bbbb','groupB')
,('cccc','groupC');
推荐答案
最简单的方法是使用 group by
和 have
:
The simplest thing would be to use group by
and having
:
SELECT group_name
FROM table2 g
JOIN table1 t on g.group_id = t.group_id
WHERE t.item in (765,656)
GROUP BY group_name
HAVING COUNT(DISTINCT t.item) = 2
这篇关于加入所有值都存在的表的 SQL 有效方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!