SQL查询 - 连接多对多关系，有选择地过滤/加入 [英] SQL query - Joining a many-to-many relationship, filtering/joining selectively

问题描述

我发现自己在SQL查询中处于一个不可行的状态，我希望我缺少某些东西，或者可能会学到新的东西。我正在使用的DB2数据库的结构并不完全用于这种查询，但是我负责这个...

I find myself in a bit of an unworkable situation with a SQL query and I'm hoping that I'm missing something or might learn something new. The structure of the DB2 database I'm working with isn't exactly built for this sort of query, but I'm tasked with this...

假设我们有表人员和表组。组可以包含多个人，一个人可以是多个组的一部分。是的，它已经很凌乱无论如何，有两个中间表连接两个。问题是，我需要从一个组列表中开始，让所有这些组中的所有人都得到，然后获得所有与之相关的组，这将是初始组集合的超集。这意味着从群体开始，加入到人群中，然后再次返回并再次加入团体。我也需要结果集中两个表的信息，以便排除一些技巧。

Let's say we have Table People and Table Groups. Groups can contain multiple people, and one person can be part of multiple groups. Yeah, it's already messy. In any case, there are a couple of intermediary tables linking the two. The problem is that I need to start with a list of groups, get all of the people in those groups, and then get all of the groups with which the people are affiliated, which would be a superset of the initial group set. This would mean starting with groups, joining down to the people, and then going BACK and joining to the groups again. I need information from both tables in the result set, too, so that rules out a number of techniques.

我必须与其他一些表一起加入信息和查询正在变得巨大，繁琐和缓慢。我想知道是否有一些方法可以从People开始，加入到Groups中，然后指定如果一个人有一组在提供的组中（通过子查询完成），那么所有组该人应该退回。我不知道有什么办法可以实现这一点，但是我在想（希望）有一个比较干净的方法可以在SQL中实现。

I have to join this with a number of other tables for additional information and the query is getting enormous, cumbersome, and slow. I'm wondering if there's some way that I could start with People, join it to Groups, and then specify that if a person has one group that is in the supplied set of groups (which is done via a subquery), then ALL groups for that person should be returned. I don't know of a way to make this happen, but I'm thinking (hoping) that there's a relatively clean way to make this happen in SQL.

快速肮脏的例子：

SELECT ...
FROM GROUPS g
  JOIN LINKING_A a 
     ON g.GROUPID = a.GROUPID
        AND GROUPID IN (subquery)
  JOIN LINKING_B b 
     ON a.GROUPLIST = b.GROUPLIST
  JOIN PEOPLE p 
     ON b.PERSONID = p.PERSONID
    --This gets me all people affiliated with groups, 
    -- but now I need all groups affiliated with those people...
  JOIN LINKING_B b2 
     ON p.PERSONID = b2.PERSONID
  JOIN LINKING_A a2 
     ON b2.GROUPLIST = a.GROUPLIST
  JOIN GROUPS g2
     ON a2.GROUPID = g.GROUPID

然后我可以从结果集中的p和g2返回信息。你可以看到我遇到麻烦的地方。这很多加入了一些大的表，更不用说在这个查询中执行的其他一些连接。我需要能够通过将PEOPLE加入到GROUPS中进行查询，然后指定如果任何人具有子查询中的关联组，则应返回所有与该对象相关联的组。我认为GROUP BY可能只是这个东西，但是我没有使用足够的真正知道。所以如果比尔是A，B和C组的一部分，而我们的子查询返回一个包含A组的集合，结果集应该包括Bill和A，B和C组。

And then I can return information from p and g2 in the result set. You can see where I'm having trouble. That's a lot of joining on some large tables, not to mention a number of other joins that are performed in this query as well. I need to be able to query by joining PEOPLE to GROUPS, then specify that if any person has an associated group that is in the subquery, it should return ALL groups affiliated with that entry in PEOPLE. I'm thinking that GROUP BY might be just the thing, but I haven't used that one enough to really know. So if Bill is part of group A, B, and C, and our subquery returns a set containing Group A, the result set should include Bill along with groups A, B, and C.

推荐答案

我想我会先建立你想要记录的人的列表，然后使用它来查询所有这些人的群体。这可以在任何数量的链接表中加入相应的连接：

I think I'd build the list of people you want to pull records for first, then use that to query out all the groups for those people. This will work across any number of link tables with the appropriate joins added:

with persons_wanted as
(
     --figure out which people are in a group you want to include
     select p.person_key
     from person p
     join link l1
     on p.person_key = l1.person_key
     join groups g
     on l1.group_key = g.group_key
     where g.group name in ('GROUP_I_WANT_PEOPLE_FROM', 'THIS_ONE_TOO')
     group by p.person_key --we only want each person_key once
)
--now pull all the groups for the list of people in at least one group we want
select p.name as person_name, g.name as group_name, ...
from person p
join link l1
on p.person_key = l1.person_key
join groups g
on l1.group_key = g.group_key
where p.person_key in (select person_key from persons_wanted);

这篇关于SQL查询 - 连接多对多关系，有选择地过滤/加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！