SQL oracle 嵌套选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14696422/
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
oracle nested select query
提问by ofir
I have this table on oracle db:
我在 oracle db 上有这个表：
Can i do nested select query that change the PARENT column to the name associated with it? like this:
我可以执行将 PARENT 列更改为与其关联的名称的嵌套选择查询吗？像这样：
I have tried:
我试过了：
SELECT PC.ID
,(
SELECT PC.NAME from PRODUCTS_CATEGORIES PC where PC.PARENT = PC.ID
)
,PC.NAME
FROM PRODUCTS_CATEGORIES PC
and got this error:
并收到此错误：
Error Message: ORA-01427: single-row sub query returns more than one row
采纳答案by rs.
You can do this
你可以这样做
SELECT PC.ID,
(
SELECT DISTINCT PC2.NAME from PRODUCTS_CATEGORIES PC2
where PC.PARENT = PC2.ID AND PC2.PARENT = 0
),
PC.NAME
FROM PRODUCTS_CATEGORIES PC
OR
或者
SELECT PC.ID,NVL(PC2.NAME,PC.PARENT) PC2_NAME,PC.NAME
FROM PRODUCTS_CATEGORIES PC
LEFT OUTER JOIN
(SELECT DISTINCT ID, NAME
from PRODUCTS_CATEGORIES WHERE PARENT = 0) PC2 ON PC.PARENT = PC2.ID
回答by a_horse_with_no_name
select pc.id,
coalesce(pr.name, 'No Parent') as parent_name,
pc.name
from products_categories pc
left join products_categories pr on pc.parent = pr.id;
Note that storing 0as the parent isn't such a good idea. It indicates that you probably don't have a foreign key constraint defined on the parentcolumn - which you should. To indicate that no parent is present it's better to use NULL(which would also work when you do have a foreign key constraint).
请注意，存储0为父级并不是一个好主意。它表明您可能没有在parent列上定义外键约束- 您应该这样做。要指示不存在父项，最好使用NULL（当您确实有外键约束时也可以使用）。
回答by jenson-button-event
Changing the alias in the subquery to say PC2would at least make the query logically easier to read.
将子查询中的别名更改为 sayPC2至少会使查询在逻辑上更易于阅读。
SELECT PC.ID
,(
SELECT PC2.NAME from PRODUCTS_CATEGORIES PC2 where PC.PARENT = PC2.ID
)
,PC.NAME
FROM PRODUCTS_CATEGORIES PC
I'm surprised oracle can work out the join since both aliases are PC. other than that the puter he don't lie - some products have more than one parent.
我很惊讶 oracle 可以计算出连接，因为两个别名都是PC. 除了推杆，他不会说谎——有些产品有不止一个父级。
You could if you didnt mind, choose the first parent (if that is indeed the problem):
如果你不介意，你可以选择第一个父母（如果这确实是问题所在）：
SELECT PC.ID
,(
SELECT PC2.NAME from PRODUCTS_CATEGORIES PC2 where PC.PARENT = PC2.ID
AND ROWNUM <= 1
)
,PC.NAME
FROM PRODUCTS_CATEGORIES PC
