postgresql 连接表和多个值的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10888097/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query for join table and multiple values
提问by Red
So I have three tables that are involved in my problem, 2 regular tables and a join table for a has many and belongs to many relationship. They look like this:
所以我有三个表涉及我的问题，2个常规表和一个连接表，其中有很多并且属于很多关系。它们看起来像这样：
table1
--id
--data
table2
--id
--data
table1_table2
--table1_id
--table2_id
So, my question is how I would query (using a join) for something that would have one or more values in the table1_table2 for one item in table1. For instance:
所以，我的问题是我将如何查询（使用连接）在 table1_table2 中对于 table1 中的一个项目具有一个或多个值的内容。例如：
Table 1
+----------+
|id | data |
+----------+
|1 | none |
+----------+
|4 | match|
+----------+
Table 2
+----------+
|id | data |
+----------+
|1 | one |
+----------+
|2 | two |
+----------+
table1_table2
+----------------------+
|table1_id | table2_id |
+----------------------+
|1 | 1 |
+----------------------+
|4 | 1 |
+----------------------+
|4 | 2 |
+----------------------+
I need a query that would match Table 1 row id 4 because it has a link via the join to both row 1 and 2 from table 2. If this is confusing please ask anything.
我需要一个与表 1 行 id 4 匹配的查询，因为它具有通过连接到表 2 中的第 1 行和第 2 行的链接。如果这令人困惑，请提出任何问题。
Maybe I was a bit unclear, I am using table1_table2 as a join not as a from. I need to make sure it matches 1 and 2 both from table 2.
也许我有点不清楚，我使用 table1_table2 作为连接而不是从。我需要确保它与表 2 中的 1 和 2 都匹配。
Here is my query so far...
到目前为止，这是我的查询...
SELECT DISTINCT table1.id,
table2.data
FROM table1
LEFT JOIN table1_table2 ON table1.id = table1_table2.table1_id
LEFT JOIN table2 ON table2.id = table1_table2.table2_id
I need a where that will make that for an entry in table 1 it matches both 1 and 2 from table 2.
我需要一个可以使表 1 中的条目匹配表 2 中的 1 和 2 的位置。
The output I am looking for would be:
我正在寻找的输出将是：
+---------------------+
|table1.id|table2.data|
+---------------------+
|4 |one |
+---------------------+
|4 |two |
+---------------------+
采纳答案by Chris Cunningham
The following approach works if you can guarantee there are no duplicates in the Table1_Table2 table. Maybe you can start here and finesse it a bit. Note how the JOIN condition works -- putting the IN in the join condition works differently than if you put the IN condition in the WHERE clause.
如果您可以保证 Table1_Table2 表中没有重复项，则以下方法有效。也许你可以从这里开始并稍微改进一下。请注意 JOIN 条件的工作原理——将 IN 放在连接条件中的工作方式与将 IN 条件放在 WHERE 子句中的工作方式不同。
I've used hash marks for values that you would need to have your code insert into the SQL.
我已经将哈希标记用于您需要将代码插入到 SQL 中的值。
SELECT Table1.id, COUNT(Table1_Table2.Table2_id)
FROM Table1
JOIN Table1_Table2 ON (Table1_Table2.Table1_id = Table1.id
AND Table1_Table2.Table2_id IN (#somelist#))
GROUP BY Table1.id
HAVING COUNT(Table1_Table2.Table2_id) = (#length of somelist#)
Oops -- you've changed your question in the way I suggested and I've ignored your edit. But this should get you started, as it returns all the Table1 id's that you are interested in.
糟糕——您已经按照我建议的方式更改了您的问题，而我忽略了您的编辑。但这应该可以帮助您入门，因为它会返回您感兴趣的所有 Table1 id。
回答by Chris Cunningham
Here's my solution. It's not very generalized. You should post a comment if this solution doesn't work because it is too specific to your situation.
这是我的解决方案。它不是很笼统。如果此解决方案不起作用，您应该发表评论，因为它太适合您的情况。
SELECT DISTINCT Table1.id
FROM Table1
INNER JOIN table1_table2 a ON (table1.id = table1_table2.table1_id
AND table2.id = 1)
INNER JOIN table1_table2 b ON (table1.id = table1_table2.table1_id
AND table2.id = 2)
回答by HLGEM
If I were doing this in SQL Server I would put the values you want to check into a temp table (or table variable) and then use a variation of what @ChrisCunningham said.
如果我在 SQL Server 中执行此操作，我会将您要检查的值放入临时表（或表变量）中，然后使用 @ChrisCunningham 所说的变体。
CREATE TABLE #temp (Id INT)
INSERT INTO #temp
VALUES (1, 3, 7)
SELECT a.id AS table1ID, table2.data
FROM
(
SELECT Table1.id, Table1_Table2.table2_id
FROM Table1 JOIN Table1_Table2
ON (Table1_Table2.Table1_id = Table1.id
AND Table1_Table2.Table2_id IN (SELECT Id FROM #temp))
GROUP BY Table1.id
HAVING COUNT(Table1_Table2.Table2_id) = (SELECT count (*) FROM #temp)
) a
JOIN Table2 ON Table2.id = a.Table2_id
Of course I'm not sure what mechanisms Postgre has for temp tables, but you would even make it a stored proc where you use some sort of split function to create the values for the temp table rather that the way I did it. But at least this might give you an idea.
当然，我不确定 Postgre 对临时表有什么机制，但您甚至可以将其设置为存储过程，您可以在其中使用某种拆分函数来创建临时表的值，而不是我这样做的方式。但至少这可能会给你一个想法。
回答by konstantin
I think you should be able to select this:
我认为你应该能够选择这个：
select table1_id, count(*) as count from table1_table2 group by table1_id having count > 2;
group by, count and having are your friends!
分组，计数和拥有是您的朋友！
