子查询返回的值超过1。当子查询跟随=时，不允许这样做！ =，<，< =，>，> =或当子查询用作表达式时。 [英] Subquery returned more than 1 value. This is not permitted when the subquery follows =, ! =, <, <= , >, >= or when the subquery is used as an expression.

问题描述

请帮忙告诉我这个。



我尝试过的事情：

Please help to give me any idea on this.

What I have tried:

declare @code nvarchar(13)
set @code= (select gendertype from cif)
if @code='001'
	begin
		set @code='Male'
	end
else if @code='002'
	begin
		set @code='Female'
	end
else if @code = '000'
	begin
		set @code='Other'
	end
select cid,name1,@code as gender from cif

推荐答案

select gendertype from cif

此语句返回多行，如果返回的超过一行然后@code有什么价值？第一个值？最后一个值？随机值？如果你想将一个变量设置为一个字段，那么你必须将它设置为一个字段。



我们无法告诉你解决方案是什么，因为我们不知道不知道你的数据或业务需求，可能是你在cif中有坏数据，你可能需要在从cif中选择时使用distinct或group by，我们无法知道。虽然看起来你可能确实需要一个CASE语句而不是



CASE（Transact-SQL） [ ^ ]



选择cid，名称，案例gendertype当...从cif结束为性别

This statement is returning more than one row, and if it is returning more than one row then what value will @code have? The first value? The last value? A random value? If you want to set a variable to a field then you have to set it to a single field.

We can't tell you what the solution is as we don't know your data or the business requirements, it could be you have bad data in cif, it could be you need to use "distinct" or "group by" when selecting from cif, we can't know. Though it looks like you might actually need a CASE statement instead

CASE (Transact-SQL)[^]

select cid, name, case gendertype when ... end as gender from cif

SELECT操作返回符合条件的所有行 - 通常由WHERE表达式提供。但是您试图将SELECT查询的结果分配给单个NVARCHAR变量 - 所以如果查询返回两行，SQL应该使用哪个值？系统不知道，所以它会抛出一个异常。

可能你要做的是添加一个WHERE条件：

A SELECT operation returns all rows that meet a criteria - normally provided by a WHERE expression. But you are trying to assign the results of a SELECT query to a single NVARCHAR variable - so if the query returns two rows, which value should SQL use? The system doesn't know, so it throws an exception instead.
Probably, what you are trying to do is add a WHERE condition:

declare @code nvarchar(13)
set @code= (select gendertype from cif WHERE somecolumn = somevalue)

甚至

Or even

declare @code nvarchar(13)
set @code= (select TOP 1 gendertype from cif WHERE somecolumn = somevalue)

但是在练习，这是一个糟糕的主意。相反，设置第二个表：GenderTypes

But in practice, that's a poor idea. Instead, set up a second table: GenderTypes

ID               Desc
(INT, IDENTITY)  NVARCHAR(6)
1                Male
2                Female
3                OTHER

并将 gendertype 列更改为INT以匹配，将GenderTypes.ID引用为foriegn键。

您的查询然后变成一个简单的JOIN：

And change your gendertype column to an INT to match, referencing the GenderTypes.ID as a foriegn key.
Your query then becomes a simple JOIN:

SELECT c.cid,c.name1,g.Desc AS gender FROM cif c
JOIN GenderTypes g ON g.ID = c.gendertype

系统将为你做腿部工作。

And the system will do the leg work for you.

这篇关于子查询返回的值超过1。当子查询跟随=时，不允许这样做！ =，<，< =，>，> =或当子查询用作表达式时。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！