单行子查询返回多于一行 oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13974889/
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
single-row subquery returns more than one row oracle
提问by javanoob
I have three tables like below:
我有如下三张表：
Test
测试
+--------------+--------+
| Test_Case_ID | Status |
+--------------+--------+
| 10 | PASS |
| 20 | FAIL |
| 30 | FAIL |
+--------------+--------+
Defect
缺点
+-----------+
| Defect_ID |
+-----------+
| 500 |
| 400 |
+-----------+
and link1
和链接1
+--------------+-----------+
| Test_Case_ID | Defect_ID |
+--------------+-----------+
| 20 | 500 |
| 30 | 500 |
| 30 | 400 |
+--------------+-----------+
I am trying the below query
我正在尝试以下查询
select
test.test_case_id,
test.status,
case when test.status = 'FAIL' then
(select link1.defect_id
from link1
where
test.test_case_id = link1.test_case_id)
end as defect1_id
from test test
I get the below error "Error 12/20/2012 10:05:17 AM 0:00:00.093 Toad for Data Analysts: ORA-01427: single-row subquery returns more than one row 1 78 "
我收到以下错误“Error 12/20/2012 10:05:17 AM 0:00:00.093 Toad for Data Analysts：ORA-01427：单行子查询返回多于一行 1 78”
Is there a way to retrieve both the records for "30" from the link table? Because i want to display that test case 30 is failing because of defect 500 & 400.
有没有办法从链接表中检索“30”的记录？因为我想显示测试用例 30 由于缺陷 500 和 400 而失败。
Thanks so much
非常感谢
采纳答案by Taryn
Have you thought about using a JOINinstead of the subquery:
你有没有想过使用 aJOIN而不是子查询：
select
t.test_case_id,
t.status,
case when t.status = 'FAIL' then l.defect_id
end as defect1_id
from test t
left join link1 l
on t.test_case_id = l.test_case_id
This will return both records, then you can decide which item to return in your final result.
这将返回两条记录，然后您可以决定在最终结果中返回哪个项目。
Result:
结果：
| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
| 20 | FAIL | 500 |
| 30 | FAIL | 500 |
| 30 | FAIL | 400 |
| 10 | PASS | (null) |
Based on your comment, if you are using Oracle 11g, then you can use the LISTAGG()function to combine the records into one row:
根据您的评论，如果您使用的是 Oracle 11g，则可以使用该LISTAGG()函数将记录合并为一行：
select
t.test_case_id,
t.status,
case
when t.status = 'FAIL'
then listagg(l.defect_id, ', ')
within group (order by l.defect_id)
end as defect1_id
from test t
left join link1 l
on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status
Result:
结果：
| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
| 10 | PASS | (null) |
| 20 | FAIL | 500 |
| 30 | FAIL | 400, 500 |
回答by Gordon Linoff
You have two rows in the link table that have values of "30". This is your problem.
链接表中有两行值为“30”。这是你的问题。
Which of these rows do you want?
您想要这些行中的哪一行？
To fix the subquery, you can either say select max(link1.defect_id)or add and rownum = 1to the whereclause.
要修复子查询，您可以说select max(link1.defect_id)或添加and rownum = 1到where子句。
What you want is probably a bi more complicated. How about this version, which concatenates the defects into a string:
你想要的可能是更复杂的。这个版本如何，它将缺陷连接成一个字符串：
select t.test_case_id, t.status,
listagg(cast(l.defect_id as varchar(32)) within group (order by l.defect_id) as defects
from test t left join
link1 l
on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status
You don't specify the version of Oracle. If listaggis not available, then wm_concatprobably is. Hereis a reference on different ways to concat strings in an aggregation in Oracle.
您没有指定 Oracle 的版本。如果listagg不可用，那么wm_concat可能是。 这是有关在 Oracle 中的聚合中连接字符串的不同方法的参考。
