子查询返回的值超过1。当子查询跟随时，不允许这样做

本文介绍了子查询返回的值超过1。当子查询跟随时，不允许这样做的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述 29岁程序员，3月因学历无情被辞！我在运行此查询时收到此错误我知道有多行查询如何以正确的方式编辑它。这就是我现在拥有的。 < code> ALTER PROCEDURE [dbo]。[Sendemailtohr ] AS BEGIN 声明 @ RequestBy varchar （ 100 ）， @EmpName varchar （ 100 ）， @ EmailToID varchar （ 100 ）， @ EmailToName varchar （ 100 ）， @ Message varchar （ 500 ）， @DocumentName varchar （ 100 ）， @ toname varchar （ 100 ）， @ EmailStatus char （ 2 ）， @ AdminName varchar （ 100 ）， @ AdminEmail varchar （ 100 ）， @ TrID int - ************************************** *************************** - SET @ EmailStatus =（从TrDocument中选择TrID请求EmailStatus为空的地方） DECLARE ADMX CURSOR FOR SELECT 名称，EmailID FROM EmployeeMaster WHERE Active = ' Y' AND 部门= ' HR' OPEN ADMX FETCH ADMX INTO @ AdminName ， @ AdminEmail WHILE @@ fetch_status = 0 BEGIN SET @ TrID =（选择 TrID 来自 TrDocumentRequest 其中 EmailStatus IS NULL 组按 TrID） SET @ DocumentName =（选择 DocumentCode 从 TrDocumentRequest 其中 TrID = @ TrID） SELECT @EmpName = Name FROM EmployeeMaster WHERE EmpID =（ SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @ TrID ） SET @ Message = ' < html>' SET @ Message = ' < html>< body> Hi' + @EmpName + ' ，文档传递提醒 - ' + @ DocumentName + ' ' SET @ Message = @ Message + ' 请提供适当的文档到 + @ toname + ' ' SET @ Message = @ Message + ' < / body>< / html>' INSERT INTO [EmailSend] （[EmailDate]，[EmailFromName]，[EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]， [留言] ，[EmailType]，[ReadIn]，[ReadOut]，[Deleted]，[Sent]，[RefTrID]，[Retry]）选择 GETDATE（），' 文档传递请求'，' [email protected]'， @ RequestBy ， @ AdminName ， @ AdminEmail ， ' 文档传递请求'， @ Message ，' DOC'，' N'，' N'，' N'，' N'， @ TrID ， 0 FETCH ADMX INTO @ AdminName ， @ AdminEmail END CLOSE ADMX DEALLOCATE ADMX 更新 TrDocumentRequest 设置 EmailStatus = ' Y' 其中 EmailStatus IS NULL END< / code> 我试图检查EmailStatus，如果它的null我想将数据插入到Emailsend表中，肯定有多行返回。请帮帮我。解决方案像这样的SQL SET @DocumentName =（选择 DocumentCode 来自 TrDocumentRequest 其中 TrID = @ TrID） SELECT @EmpName = Name FROM EmployeeMaster WHERE EmpID =（ SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @ TrID ）如果（select ...）返回多条记录，则将失败，即有多个TrDocumentRequest与TrID匹配@论坛报。我们无法访问您的数据，因此请仔细检查所有子查询以查看哪些子查询返回多行并解决该问题，无论它是什么。也许你有你不知道的流氓数据，也许你需要选择前1而不是......无论什么都能解决你的具体问题。你的方法存在缺陷，而不是迭代HR管理员用户，你应该迭代文档。让我们对您的代码进行一步一步的优化迭代更正和第一次优化迭代 DECLARE @ TBL TABLE （ TRID INT ， DOCUMENT_NAME VARCHAR （ 100 ）， EMP_NAME VARCHAR （ 100 ）， TO_NAME VARCHAR （ 100 ）， MESSAGE_BODY VARCHAR （ 1024 ）） INSERT INTO @ TBL （TRID，DOCUMENT_NAME， EMP_NAME，TO_NAME，MESSAGE_BODY）选择 TDR.TrID，TDR。 DocumentCode，EM.NAME，' '，' < html>< body>嗨' + EM.NAME + ' ，文档传送提示 - ' + TDR.DocumentCode + ' 请将适当的文档发送到' + @ toname + ' < / body>< / html>' From （ SELECT DISTINCT TRID FROM TrDocumentRequest 其中 EmailStatus IS NULL ）INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID DECLARE ADMX CURSOR FOR SELECT 名称，EmailID FROM EmployeeMaster WHERE Active = ' Y' AND 部门= ' HR' OPEN ADMX FETCH ADMX INTO @AdminName ， @ AdminEmail WHILE @@ fetch_status = 0 BEGIN INSERT INTO [EmailSend] （[EmailDate]，[EmailFromName]，[ EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]， [留言]，[EmailType]，[ReadIn]，[ReadOut]，[已删除]，[已发送]，[RefTrID] ，[重试]）选择 GETDATE（），' 文档传递请求， [email protected]'， @ RequestBy ， @ AdminName ， @ AdminEmail ， ' 文件传送请求'，MESAGE_BODY，' DOC'，' N'，' N'，' N'，' N'，TRID， 0 FROM @ TBL FETCH ADMX INTO @ AdminName ， @ AdminEmail END CLOSE ADMX DEALLOCATE ADMX 在制作中，您将始终拥有更多未经电子邮件处理的文档请求加工服务。由于这与上下文无关（意味着不依赖于用户），更好的方法是将所有候选文档请求保存在临时位置并同时生成消息正文并执行批量插入过程如上所示第二次优化迭代如果你仔细查看代码，您可以确认可以消除整个循环。循环只是获取hr管理员用户并将其标记为记录消息队列中的请求。 DECLARE @ TBL TABLE （ TRID INT ， DOCUMENT_NAME VARCHAR （ 100 ）， EMP_NAME VARCHAR （ 100 ）， TO_NAME VARCHAR （ 100 ）， MESSAGE_BODY VARCHAR （ 1024 ）） - ************ ******************* - SET @ EmailStatus =（从TrDocumentRequest中选择TrID在哪里EmailStatus IS NULL） INSERT INTO @ TBL （TRID，DOCUMENT_NAME，EMP_NAME，TO_NAME，MESSAGE_BODY）选择 TDR.TrID， TDR.DocumentCode，EM.NAME，' '，' < html>< body>嗨' + EM.NAME + ' ，文档传递警报 - ' + TDR.DocumentCode + ' 请将适当的文件发送到' + @ toname + ' < / body>< / html>' From （ SELECT DISTINCT TRID FROM TrDocumentRequest 其中 EmailStatus IS NULL ）INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR .TRID INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID INSERT INTO [EmailSend ] （[EmailDate]，[EmailFromName]，[EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]， [留言]，[EmailType]，[ReadIn]， [ReadOut]，[已删除]，[已发送]，[RefTrID]，[重试]）选择 GETDATE（），' 文档传递请求'，' [email protected]'， @ RequestBy ，名称，EmailID， ' 文档传递请求'，MESSAGE_BODY，' DOC'，' N'，' N'，' N'， ' N'，TRID， 0 FROM @ TBL T CROSS JOIN （ SELECT 名称，EmailID FROM EmployeeMaster WHERE Active = ' Y' AND 部门= ' HR'）A 更新 TrDocumentRequest 设置 EmailStatus = ' Y' 其中 EmailStatus IS NULL 我还注意到有些变量没有像@RequestBy那样正确初始化 i am receiving this error while am running this query i know there is multiple rows for query how i can edit it in proper way.This is what i have now.<code>ALTER PROCEDURE [dbo].[Sendemailtohr]ASBEGINDeclare @RequestBy varchar(100), @EmpName varchar(100), @EmailToID varchar(100), @EmailToName varchar(100), @Message varchar(500), @DocumentName varchar(100), @toname varchar(100), @EmailStatus char(2), @AdminName varchar(100), @AdminEmail varchar(100), @TrID int--***************************************************************** --SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL)DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'OPEN ADMXFETCH ADMX INTO @AdminName, @AdminEmailWHILE @@fetch_status = 0BEGIN SET @TrID = (Select TrID From TrDocumentRequest Where EmailStatus IS NULL Group By TrID) SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID) SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)SET @Message = '<html>'SET @Message = '<html><body>Hi ' + @EmpName + ',Document Delivery Alert - ' + @DocumentName + ''SET @Message = @Message + 'Please Deliver the Appropriate Document To ' + @toname + ''SET @Message = @Message + '</body></html>'INSERT INTO [EmailSend] ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject], [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry]) Select GETDATE(),'Document Delivery Request','[email protected]',@RequestBy,@AdminName,@AdminEmail, 'Document Delivery Request',@Message,'DOC','N','N','N','N',@TrID,0FETCH ADMX INTO @AdminName, @AdminEmailENDCLOSE ADMXDEALLOCATE ADMXUpdate TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULLEND</code>I trying to check the EmailStatus if its null i want to insert data to Emailsend table, surely there is multiple rows returning. please help me out. 解决方案 SQL like thisSET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)will fail if the "(select ...)" returns more than one record, ie there is more than one TrDocumentRequest with a TrID matching @TrID. We can't access your data, so go through all the sub-queries to see which ones are returning multiple rows and solve that issue, whatever it is. Maybe you have rogue data you don't know about, maybe you need to "select top 1" instead....whatever will solve your specific issue.There are flaws in your approach, Instead of iterating over the HR Admin users you should have iterated over Documents. Let's do a step by step optimization iteration over your codeCorrection and first Optimization IterationDECLARE @TBL TABLE(TRID INT,DOCUMENT_NAME VARCHAR(100),EMP_NAME VARCHAR(100),TO_NAME VARCHAR(100),MESSAGE_BODY VARCHAR(1024))INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body>Hi ' + EM.NAME + ',Document Delivery Alert - ' + TDR.DocumentCode + 'Please Deliver the Appropriate Document To ' + @toname + '</body></html>'From(SELECT DISTINCT TRIDFROM TrDocumentRequestWhere EmailStatus IS NULL) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRIDINNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpIDDECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'OPEN ADMXFETCH ADMX INTO @AdminName, @AdminEmailWHILE @@fetch_status = 0BEGININSERT INTO [EmailSend] ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject], [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry]) Select GETDATE(),'Document Delivery Request','[email protected]',@RequestBy,@AdminName,@AdminEmail, 'Document Delivery Request',MESAGE_BODY,'DOC','N','N','N','N',TRID,0 FROM @TBLFETCH ADMX INTO @AdminName, @AdminEmailENDCLOSE ADMXDEALLOCATE ADMXIn production you will always have more Document Requests which have not been processed by your email processing service. Since this is independent of the context (means not depending over users) a better approach would be to hold all candidate document requests in a temp locationand generating message body as well in the same time and do a bulk insert process like shown aboveSecond Optimization IterationIf you closely look at the code, you can acknowledge that the whole loop can be eliminated. The loop is simply fetching hr admin users and tagging it to document requests in message queue.DECLARE @TBL TABLE(TRID INT,DOCUMENT_NAME VARCHAR(100),EMP_NAME VARCHAR(100),TO_NAME VARCHAR(100),MESSAGE_BODY VARCHAR(1024))--*****************************************************************--SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL)INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body>Hi ' + EM.NAME + ',Document Delivery Alert - ' + TDR.DocumentCode + 'Please Deliver the Appropriate Document To ' + @toname + '</body></html>'From(SELECT DISTINCT TRIDFROM TrDocumentRequestWhere EmailStatus IS NULL) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRIDINNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpIDINSERT INTO [EmailSend] ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject], [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])Select GETDATE(),'Document Delivery Request','[email protected]',@RequestBy,Name,EmailID,'Document Delivery Request',MESSAGE_BODY,'DOC','N','N','N','N',TRID,0FROM @TBL T CROSS JOIN(SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR') AUpdate TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULLI have also noticed that some variables are not initialized appropriately like @RequestBy 这篇关于子查询返回的值超过1。当子查询跟随时，不允许这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！上岸，阿里云！