通过自联接替换嵌套的选择查询

I recently asked a question here concerning an SQL query: Trouble wrapping head around complex SQL delete query

我最近在这里问了一个关于SQL查询的问题:麻烦包围复杂的SQL删除查询

I now understand that what I'm trying to do is too complex to pull off with a single query or even multiple queries without some way to keep results in between. Therefore I decided to create a bash script (the end result will be something to do with a cronjob so bash is the most straightforward choice).

我现在明白,我想要做的事情太复杂了,无法通过单个查询甚至多个查询来实现,而无需在某种程度上保持结果。因此我决定创建一个bash脚本(最终结果将与cronjob有关,因此bash是最直接的选择)。

Consider the following table:

请考虑下表:

AssociatedClient:

+-----------+-----------------+
| Client_id | Registration_id |
+-----------+-----------------+
|         2 |               2 | 
|         3 |               2 | 
|         3 |               4 | 
|         4 |               5 | 
|         3 |               6 | 
|         5 |               6 | 
|         3 |               8 | 
|         8 |               9 | 
|         7 |              10 | 
+-----------------------------+

What I want to do is select all Registration_ids where the Client_id is in the list of Client_ids associated with a specific Registration_id.

我想要做的是选择所有Registration_ids,其中Client_id位于与特定Registration_id相关联的Client_id列表中。

Although I'm pretty noob with SQL, I found this query relatively easy:

虽然我很喜欢SQL,但我发现这个查询相对容易:

SELECT `Registration_id` FROM `AssociatedClient` ac1
WHERE ac1.`Client_id` IN
                      (SELECT `Client_id` FROM `AssociatedClient` ac2 
                       WHERE ac2.`Registration_id` = $reg_id);

where $reg_id is just a bash variable.

其中$ reg_id只是一个bash变量。

This works but I would like to see it done with a self join, because it looks nicer, especially within a bash script where a lot of character clutter occurs. I'm afraid my SQL skills just don't reach that far.

这有效,但我希望看到它通过自联接完成,因为它看起来更好,尤其是在出现大量字符混乱的bash脚本中。我担心我的SQL技能不会达到那么远。

1 个解决方案

#1

If I've understood correctly, you should just be able to do a simple self join like so:

如果我理解正确,你应该能够像这样做一个简单的自我联接:

SELECT ac1.registration_id
FROM associatedclient ac1
JOIN associatedclient ac2 ON ac2.client_id = ac1.client_id
WHERE ac2.registration_id = $reg_id

So what you are doing is scanning the table once, joining it to itself where the client_id matches. Then you are restricting the joined rows to ones where the 2nd version of the table has a specific id, leaving you with the different permutations of the join on the 1st table, and then just picking the registration_id from those rows.

所以你要做的就是扫描一次表,将它连接到client_id匹配的地方。然后,您将连接的行限制为表的第二个版本具有特定ID的那些行,在第一个表上保留连接的不同排列,然后从这些行中选择registration_id。

So, given the example of a variable value of 6, try running the following statement:

因此,给定变量值为6的示例,请尝试运行以下语句:

SELECT 
  ac1.client_id AS client_id_1
, ac1.registration_id AS reg_id_1
, ac2.client_id AS client_id_2
, ac2.registration_id AS reg_id_2
FROM associatedclient ac1
JOIN associatedclient ac2 ON ac1.client_id = ac2.client_id

and you'll notice the full set of joins. Then try adding the WHERE restriction and notice which rows come back. Then finally just pick the column you want.

你会注意到完整的连接集。然后尝试添加WHERE限制并注意哪些行返回。然后最后选择你想要的列。

You can check out a SQLFiddle I set up which tests it with a value of 6

您可以查看我设置的SQLFiddle,它使用值6对其进行测试

#1