SQL 如何修复 Ora-01427 单行子查询在选择中返回多于一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21397694/
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
How to fix Ora-01427 single-row subquery returns more than one row in select?
提问by Sam1604
When i execute the following query, i get the message like
当我执行以下查询时，我收到类似的消息
"Ora-01427 single-row subquery returns more than one row"
“Ora-01427 单行子查询返回多于一行”
SELECT E.I_EmpID AS EMPID,
E.I_EMPCODE AS EMPCODE,
E.I_EmpName AS EMPNAME,
REPLACE(TO_CHAR(A.I_REQDATE, 'DD-Mon-YYYY'), ' ', '') AS FROMDATE,
REPLACE(TO_CHAR(A.I_ENDDATE, 'DD-Mon-YYYY'), ' ', '') AS TODATE,
TO_CHAR(NOD) AS NOD,
DECODE(A.I_DURATION,
'FD',
'FullDay',
'FN',
'ForeNoon',
'AN',
'AfterNoon') AS DURATION,
L.I_LeaveType AS LEAVETYPE,
REPLACE(TO_CHAR((SELECT C.I_WORKDATE
FROM T_COMPENSATION C
WHERE C.I_COMPENSATEDDATE = A.I_REQDATE
AND C.I_EMPID = A.I_EMPID),
'DD-Mon-YYYY'),
' ',
'') AS WORKDATE,
A.I_REASON AS REASON,
AP.I_REJECTREASON AS REJECTREASON
FROM T_LEAVEAPPLY A
INNER JOIN T_EMPLOYEE_MS E
ON A.I_EMPID = E.I_EmpID
AND UPPER(E.I_IsActive) = 'YES'
AND A.I_STATUS = '1'
INNER JOIN T_LeaveType_MS L
ON A.I_LEAVETYPEID = L.I_LEAVETYPEID
LEFT OUTER JOIN T_APPROVAL AP
ON A.I_REQDATE = AP.I_REQDATE
AND A.I_EMPID = AP.I_EMPID
AND AP.I_APPROVALSTATUS = '1'
WHERE E.I_EMPID <> '22'
ORDER BY A.I_REQDATE DESC
when i execute this without ORDER BY A.I_REQDATE DESC it returns 100 rows...
当我在没有 ORDER BY A.I_REQDATE DESC 的情况下执行此操作时，它返回 100 行...
回答by hashbrown
Use the following query:
使用以下查询：
SELECT E.I_EmpID AS EMPID,
E.I_EMPCODE AS EMPCODE,
E.I_EmpName AS EMPNAME,
REPLACE(TO_CHAR(A.I_REQDATE, 'DD-Mon-YYYY'), ' ', '') AS FROMDATE,
REPLACE(TO_CHAR(A.I_ENDDATE, 'DD-Mon-YYYY'), ' ', '') AS TODATE,
TO_CHAR(NOD) AS NOD,
DECODE(A.I_DURATION,
'FD',
'FullDay',
'FN',
'ForeNoon',
'AN',
'AfterNoon') AS DURATION,
L.I_LeaveType AS LEAVETYPE,
REPLACE(TO_CHAR((SELECT max(C.I_WORKDATE)
FROM T_COMPENSATION C
WHERE C.I_COMPENSATEDDATE = A.I_REQDATE
AND C.I_EMPID = A.I_EMPID),
'DD-Mon-YYYY'),
' ',
'') AS WORKDATE,
A.I_REASON AS REASON,
AP.I_REJECTREASON AS REJECTREASON
FROM T_LEAVEAPPLY A
INNER JOIN T_EMPLOYEE_MS E
ON A.I_EMPID = E.I_EmpID
AND UPPER(E.I_IsActive) = 'YES'
AND A.I_STATUS = '1'
INNER JOIN T_LeaveType_MS L
ON A.I_LEAVETYPEID = L.I_LEAVETYPEID
LEFT OUTER JOIN T_APPROVAL AP
ON A.I_REQDATE = AP.I_REQDATE
AND A.I_EMPID = AP.I_EMPID
AND AP.I_APPROVALSTATUS = '1'
WHERE E.I_EMPID <> '22'
ORDER BY A.I_REQDATE DESC
The trick is to force the inner query return only one record by adding an aggregate function (I have used max() here). This will work perfectly as far as the query is concerned, but, honestly, OP should investigate why the inner query is returning multiple records by examining the data. Are these multiple records really relevant business wise?
诀窍是通过添加一个聚合函数来强制内部查询只返回一条记录（我在这里使用了 max() ）。就查询而言，这将完美地工作，但是，老实说，OP 应该通过检查数据来调查为什么内部查询返回多条记录。这些多条记录真的与商业相关吗？
回答by StuartLC
The only subquery appears to be this - try adding a ROWNUMlimit to the where to be sure:
唯一的子查询似乎是这个 - 尝试添加一个ROWNUM限制，以确保：
(SELECT C.I_WORKDATE
FROM T_COMPENSATION C
WHERE C.I_COMPENSATEDDATE = A.I_REQDATE AND ROWNUM <= 1
AND C.I_EMPID = A.I_EMPID)
You do need to investigate why this isn't unique, however - e.g. the employee might have had more than one C.I_COMPENSATEDDATEon the matched date.
但是，您确实需要调查为什么这不是唯一的 - 例如，员工可能C.I_COMPENSATEDDATE在匹配的日期有多个。
For performance reasons, you should also see if the lookup subquery can be rearranged into an inner / left join, i.e.
出于性能原因，您还应该查看查找子查询是否可以重新排列为内部/左连接，即
SELECT
...
REPLACE(TO_CHAR(C.I_WORKDATE, 'DD-Mon-YYYY'),
' ',
'') AS WORKDATE,
...
INNER JOIN T_EMPLOYEE_MS E
...
LEFT OUTER JOIN T_COMPENSATION C
ON C.I_COMPENSATEDDATE = A.I_REQDATE
AND C.I_EMPID = A.I_EMPID
...
