一个小问题,求解

[cy527300280]

 declare @gz money,@mgz money
        set @gz=(select avg(目前薪资) from 黄金员工表)
        set @mgz =(select 目前薪资 from 黄金员工表)
        select @gz
          

报错:
服务器: 消息 512，级别 16，状态 1，行 3
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

（所影响的行数为 1 行）

[liangCK]

set @mgz =(select 目前薪资 from 黄金员工表)   --请问黄金员工表有多条记录的时候..你想让系统返回哪个?

[liangCK]

set @mgz =(select top 1 目前薪资 from 黄金员工表)

[rucypli]

select 目前薪资 from 黄金员工表 返回的是结果集

[liangCK]

set @mgz =(select top 1 目前薪资 from 黄金员工表)

[等不到来世]

select 目前薪资 from 黄金员工表
返回多个值，系统不知道把哪个赋给@mgz,因此报错。

set @mgz =(select top 1 目前薪资 from 黄金员工表)

[cy527300280]

有没有办法可以让多个值给一个变量?

[等不到来世]

[Quote=引用 6 楼 cy527300280 的回复:]
有没有办法可以让多个值给一个变量?
[/Quote]

if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab

go

SQL2005用XML:

方法1:

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from 
    (select distinct COl1 from Tab) a
Cross apply
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b

方法2:

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from 
    (select distinct COl1 from Tab) a
cross apply
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)
                .query('<Tab>
                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
                {concat("",string(/Tab[last()]/@COl2))}
                </Tab>')
                )b

SQL05用CTE:

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)


生成结果:
/*
Col1        COl2
----------- ------------
1           a,b,c
2           d,e
3           f

(3 行受影响)
*/