SQL子查询返回多个值。帮我解决这个Sql查询 [英] SQL subquery return more than 1 value. Help me solve with this Sql query

问题描述

这是我的SQL查询。

this is my SQL query.

"select Unit from UnitConversion "+
               "where uocNo=(select uocNo from UnitConversionProduct "+
               "where UnitConversionProduct.ProductNo=@ProductNo)"

UnitConversion表格包含以下栏目

uocNo

单位

金额

基本



UnitConversionProduct表有以下栏目

uocNo

ProductNo



执行该查询时出现以下错误



子查询返回的值超过1。当子查询跟随=，！=，<，< =，>，> =或子查询为子时，不允许这样做用作表达式。

UnitConversion table has following columns
uocNo
Unit
Amount
Base

UnitConversionProduct table has following columns
uocNo
ProductNo

when i execute that query the following error appear

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

推荐答案

当您在where子句中使用=，！=，<，< =，>，> =时，您可以将它（此处为uocNo）与一个值进行比较，但在查询中它返回多个值。（尝试运行此

When you use =,!=, <, <= , >, >= in where clause you can compare it(here uocNo) to only one value , but in your query it`s return more than one value.(Try to run this

select uocNo from UnitConversionProduct "+
               "where UnitConversionProduct.ProductNo=@ProductNo

您将获得多个值。）对于解决方案，您必须确保子查询必须只返回一个值。我认为你必须拥有单个ProductNo的多个值。

you will get more than one value.) For solutions you have to ensure that subquery must return only one value. I think you must have multiple value for single ProductNo.

你得到的错误信息非常自我解释。它失败了，因为你的SubQuery返回了多个值。尝试以下两种查询之一。

Error message you are getting is very well self explanatory. Its failing because your SubQuery returning more than one value. Try either of below two queries.

SELECT Unit FROM UnitConversion where uocNo in(select uocNo from UnitConversionProduct WHERE UnitConversionProduct.ProductNo=@ProductNo)

OR

OR

SELECT Unit FROM UnitConversion UC INNER JOIN UnitConversionProduct UCP ON UC.uocNo = UCP.uocNo WHERE UCP.ProductNo = @ProductNo

我建议你使用第二个查询becuase Inner Join比较时性能更快to SubQuery。



希望它有所帮助！

I recommend you to use second query becuase Inner Join is faster in performance as compare to SubQuery.

Hope it helps!

你可以在子查询中使用：

1） TOP（） [ ^ ]声明

You can use in subquery:
1) TOP()[^] statement

SELECT Unit
FROM UnitConversion
    WHERE uocNo=(SELECT TOP(1) uocNo
                 FROM UnitConversionProduct
                 WHERE UnitConversionProduct.ProductNo=@ProductNo)

或

2）DISTINCT [ ^ ]声明

or
2) DISTINCT[^] statement

SELECT Unit
FROM UnitConversion
    WHERE uocNo=(SELECT DISTINCT uocNo
                 FROM UnitConversionProduct
                 WHERE UnitConversionProduct.ProductNo=@ProductNo)

在大多数情况下使用TOP和/或DISTINCT应该有助于返回1条记录。

In most cases using TOP and/or DISTINCT should helps to return 1 record.

这篇关于SQL子查询返回多个值。帮我解决这个Sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！