sql - oracle嵌套选择查询

Question

我在 oracle 数据库上有这个表:

我可以执行嵌套选择查询，将 PARENT 列更改为与其关联的名称吗？像这样:

我试过:

SELECT PC.ID
,(
  SELECT PC.NAME from PRODUCTS_CATEGORIES PC where PC.PARENT = PC.ID
 )
 ,PC.NAME
  FROM PRODUCTS_CATEGORIES PC 

得到这个错误:

Error Message: ORA-01427: single-row sub query returns more than one row

Answer 1

你可以这样做

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 

或

 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