oracle 在嵌套SQL查询的计算中使用列值

col17t5w 于 2023-05-22 发布在 Oracle

关注(0)|答案(1)|浏览(142)

我有以下SQL查询：

SELECT
   (SELECT CATEGORY_NAME FROM tableA WHERE NUM_CRITERIA != 0) AS "Name",
   (numYes / (criteriaCount * numParts - numNA)) / numParts AS "Progress"
FROM (
   SELECT
      (SELECT NUM_CRITERIA FROM tableA WHERE NUM_CRITERIA != 0) AS criteriaCount,
      (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'Yes' AND ASSEMBLY_ID = 1) AS numYes,
      (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'N/A' AND ASSEMBLY_ID = 1) AS numNA,
      (SELECT COUNT(*) FROM tableC WHERE ASSEMBLY_ID = 1) AS numParts
   FROM DUAL
)

此查询的目标是从各个表中选择列，并使用这些列的值来计算“Progress”列的值。我需要“Progress”列来在代码中计算criteriaCount列的每个值。这段代码在oracle apex中编译得很好，但是当它运行时，我得到了一个ORA-01427错误，因为criteriaCount查询返回了多行。
任何帮助将不胜感激

oracle

来源：https://stackoverflow.com/questions/76266796/using-a-column-value-in-a-calculation-for-a-nested-sql-query

1条答案

按热度按时间

ktca8awb1#

由于TableA的查询可以返回多行（这是您想要的），因此不能将其与其他查询一起放置为 * 标量子查询 *。
您需要直接查询TableA并将结果与其他子查询连接。
例如：

SELECT
 a.NUM_CRITERIA, 
 a.category_name as name,
 (b.numYes / (a.NUM_CRITERIA * b.numParts - b.numNA)) / b.numParts AS "Progress"
FROM tableA a
cross join (
  SELECT
    (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'Yes' AND ASSEMBLY_ID = 1) AS numYes,
    (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'N/A' AND ASSEMBLY_ID = 1) AS numNA,
    (SELECT COUNT(*) FROM tableC WHERE ASSEMBLY_ID = 1) AS numParts
  FROM DUAL
) b
WHERE a.NUM_CRITERIA <> 0

赞(0）回复(0）举报 2023-05-22

我来回答

oracle 在嵌套SQL查询的计算中使用列值

1条答案

相关问题

热门标签

最新问答