oracle SQL 加入同一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11420582/
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 Join in the same table
提问by jim collins
I have a table with the following columns
我有一个包含以下列的表格
- product_types
- brand_ids
- 产品类型
- 品牌 ID
I need the brand_idsA and B, but not C
我需要brand_idsA和B，但不需要C
In this table, can have multiple records with the same product_type, but with different brand_ids.
在这个表中，可以有多条产品类型相同但品牌标识不同的记录。
Could it be the need of a INNER SELF JOIN? or is there a better way to do so?
是否需要内部自连接？或者有更好的方法吗？
回答by Set
SELECT A.product_type
FROM product_table A
JOIN product_table B ON A.product_type = B.product_type
LEFT JOIN product_table C ON A.product_type = C.product_type
AND c.brand_id = 'C'
WHERE A.brand_id = 'A'
AND B.brand_id = 'B'
AND c.brand_id IS NULL
回答by Dave Costa
I'm going to assume that (PRODUCT_TYPE, BRAND_ID)is a unique key of your table.
我将假设这(PRODUCT_TYPE, BRAND_ID)是您表的唯一键。
Here's one method to do what I think you are after:
这是做我认为您所追求的一种方法：
SELECT product_type FROM product_table
WHERE brand_id IN ('A','B')
GROUP BY product_type
HAVING COUNT(*) = 2
MINUS
SELECT product_type FROM product_table
WHERE brand_id IN ('C')
回答by Zane Bien
You can try this solution:
你可以试试这个解决方案：
SELECT a.product_type
FROM
(
SELECT product_type
FROM tbl
WHERE brand_id IN ('A', 'B')
GROUP BY product_type
) a
LEFT JOIN tbl b ON a.product_type = b.product_type AND b.brand_id = 'C'
WHERE b.brand_id IS NULL
