Sql子查询返回多个值 [英] Sql subquery returning more than one value
问题描述
大家好
有人可能会帮助我，我想弄明白我的问题。我在运行以下存储过程时遇到以下错误。
Hi everyone
Could someone maybe help me i am trying to figure out my problem. Im getting the below error when running the below stored proc.
子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
USE [保修]
GO
/ ******对象：StoredProcedure [dbo]。[spProductDetailsInsert]脚本日期：01/22/2015 11:27:06 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo]。[spProductDetailsInsert ]
@UserId int
AS
BEGIN
声明@IdentityOutput表（ID int）
开始
INSERT INTO dbo.tblProductDetails（DateLoaded，Order_Number，Shipping_Date，SKU，Product_Description，Product_Supplier，Product_Group，Serial_Number，Client，IMEI，Warranty_Period，UserId）输出inserted.ID到@IdentityOutput
SELECT GETDATE（），Order_Number，S hipping_Date，SKU，Product_Description，
（选择来自tblProductSupplier，其中Supplier = sm.Product_Supplier）作为[Product_Supplier]，（选择来自tblProduct_Group的Id，其中[Group] = sm.Product_Group）作为[Product_Group]， Serial_Number，
（选择来自tblClient的ClientID，其中Client_name = sm.Client）为[Client]，IMEI，
（从tblWarrantyPeriod中选择Id，其中Period = sm.Warranty_Period）as [Warranty_Period]，@ UserId
来自dbo.tblTmpProductDetails sm
其中sm.IsError为空
insert INTO tblCustomer（ProductId，FullName，Cell，Email，Landline，UserId）
select（从@IdentityOutput中选择ID），CustomerName，CustomerCell，CustomerEmail，CustomerLandLine，@ UserId
来自tblTmpProductDetails
结束
从tblTmpProductDetails删除
END
USE [warranty]
GO
/****** Object: StoredProcedure [dbo].[spProductDetailsInsert] Script Date: 01/22/2015 11:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spProductDetailsInsert]
@UserId int
AS
BEGIN
declare @IdentityOutput table ( ID int )
Begin
INSERT INTO dbo.tblProductDetails(DateLoaded, Order_Number,Shipping_Date,SKU,Product_Description,Product_Supplier,Product_Group,Serial_Number,Client,IMEI,Warranty_Period,UserId)output inserted.ID into @IdentityOutput
SELECT GETDATE(), Order_Number,Shipping_Date,SKU,Product_Description,
(select id from tblProductSupplier where Supplier =sm.Product_Supplier) as [Product_Supplier],(select Id from tblProduct_Group where [Group] = sm.Product_Group) as [Product_Group],Serial_Number,
(select ClientID from tblClient where Client_name = sm.Client) as [Client],IMEI,
(select Id from tblWarrantyPeriod where Period = sm.Warranty_Period) as [Warranty_Period],@UserId
FROM dbo.tblTmpProductDetails sm
where sm.IsError is null
insert INTO tblCustomer(ProductId,FullName,Cell,Email,Landline,UserId)
select (select ID from @IdentityOutput),CustomerName,CustomerCell,CustomerEmail,CustomerLandLine,@UserId
from tblTmpProductDetails
end
delete from tblTmpProductDetails
END
推荐答案
你有几个subq可能导致此问题的ueries。例如
You have several subqueries that can cause this problem. For example
SELECT GETDATE(), Order_Number,Shipping_Date,SKU,Product_Description,
(select id from tblProductSupplier where Supplier =sm.Product_Supplier) as [Product_Supplier],...
如果供应商= sm.Product_Supplier产生的条件生成此错误超过1行。
因此请浏览所有子查询并检查它们是否与外部查询正确相关。
另外，在所有条件下使用表别名以避免条件中的不确定性。
if the condition where Supplier =sm.Product_Supplier produces more than 1 row this error is generated.
So go through all the subqueries and check that they are correctly correlated to the outer query.
Also, use table aliases in all conditions inn order to avoid ambiquity in conditions.
显然错误说你的一个子查询返回多个结果
好像你的一张桌子上没有主钥匙
所以你做了以下步骤
只需检查一下以下查询
obviously the error says one of your subquery is return more than one result
Seems one of your table does not have a primary key
so you have do the following steps
just check the following queries
select Client_name, Count(*) from tblClient
Group by Client_name
Having count(*)>1
select Supplier, count (*) from tblProductSupplier
Group by Supplier
Having count(*)>1
select [Group], count (*) from tblProduct_Group
Group by [Group]
Having count(*)>1
select Period , count (*) from tblWarrantyPeriod
Group by Period
Having count(*)>1
如果以上任何一个查询返回结果，那么你需要检查该表中的数据
（请记住我刚刚给你一个方法解决这个问题）
i感觉第一个查询返回结果在这种情况下尝试在查询中用clientid而不是客户端名称重新创建查询
像这样
if any of the above query return results then you need to check the data in that table
(keep it in mind i have just give you a way to solve this issue)
i feel the first query return results in that case try to recreate the query with clientid instead of client name in your query
like this
(select ClientID from tblClient where ClientID = sm.Clientid)
希望你理解;）
Hope you understand ;)
这篇关于Sql子查询返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
