优化 SQL 语句以使用查询，但需要以某种方式引用子查询之外的列

Question

Oracle 有没有办法在 SQL 查询中引用子查询外部的列，或者即使通过不同的方式也能达到这种效果？到目前为止我在网上读到的所有内容都对此没有帮助。

例如（这就是我所追求的事情）：

SELECT a.product_code, b.received, b.issued
FROM productinfo a,
 (SELECT SUM(qty_received) AS received, SUM(qty_issued) AS issued
  FROM productdetail b WHERE b.product_code = a.product_code AND active = 1);

我已经尝试了很多不同的变体/组合，目前我只是收到类似 ORA-00904 的错误：与 WHERE 子句关系相关的标识符无效。

目前，如果我作为单独的查询运行，例如：

SELECT product_code FROM productinfo;

然后对于每条记录：

SELECT SUM(qty_received) AS received, SUM(qty_issued) AS issued FROM productdetail
WHERE product_code = '(specified)' AND active = 1;

这可能需要半个小时以上才能运行 8000 条记录，这简直是愚蠢的。

头发用完了，感谢任何帮助！谢谢。

Answer 1

ORA 错误是因为您无法关联派生表/内联视图 - 您需要使用 JOIN 表示法（ANSI-89 或 92）。

假设您确实需要 PRODUCTINFO 表中的列，请使用：

SELECT a.product_code, b.received, b.issued
  FROM PRODUCTINFO a
  JOIN (SELECT t.product_code,
               SUM(t.qty_received) AS received, 
               SUM(t.qty_issued) AS issued
          FROM PRODUCTDETAIL t 
         WHERE t.active = 1
      GROUP BY t.produce_code) b ON b.product_code = a.product_code

如果您想查看 ProductInfo 记录的列表（可能有也可能没有 PRODUCTDETAIL 记录），请使用：

   SELECT a.product_code, b.received, b.issued
     FROM PRODUCTINFO a
LEFT JOIN (SELECT t.product_code,
                  SUM(t.qty_received) AS received, 
                  SUM(t.qty_issued) AS issued
             FROM PRODUCTDETAIL t 
            WHERE t.active = 1
         GROUP BY t.produce_code) b ON b.product_code = a.product_code

但示例看起来像你可能只需要使用：

 SELECT t.product_code,
        SUM(t.qty_received) AS received, 
        SUM(t.qty_issued) AS issued
   FROM PRODUCTDETAIL t 
   WHERE t.active = 1
GROUP BY t.produce_code

Answer 2

只需在查询内部进行分组并使用内部联接

SELECT a.product_code, b.received, b.issued
FROM productinfo a
INNER JOIN
 (SELECT product_code, SUM(qty_received) AS received, SUM(qty_issued) AS issued
  FROM productdetail
  WHERE active = 1
  GROUP BY product_code) b on b.product_code = a.product_code