插入语句如下:INSERT INTO tblVOTEWinnerList
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
( select '111',222,'333','444',
PhoneNumber =(select top 3 Voter from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' order by newid())
from tblVOTEDrawingInfo where DrawingID=‘222’) 就是随机查询到 前三名的Voter ,然后插入到tblVOTEWinnerList的字段中,前面的ID都要求是一样的,我这个报错:
“子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。”求解释该如何修改。
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
( select '111',222,'333','444',
PhoneNumber =(select top 3 Voter from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' order by newid())
from tblVOTEDrawingInfo where DrawingID=‘222’) 就是随机查询到 前三名的Voter ,然后插入到tblVOTEWinnerList的字段中,前面的ID都要求是一样的,我这个报错:
“子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。”求解释该如何修改。
下边这个查询取出3条记录,
肯定报错啊(select top 3 Voter from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' order by newid())
from tblVOTEDrawingInfo where DrawingID=‘222’)
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
( select top 3 '111',222,'333','444',
PhoneNumber =(select Voter from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' order by newid())
from tblVOTEDrawingInfo where DrawingID=‘222’)
取多名,就循环下,分别插入。给个大概意思,自己改改吧。
比如取 3条,插入
declare @i int---计数器
declare @n int
declare @str nvarchar(50)
set @i=1select top 3 Voter from tblVOTEVoteRecord a left join ......--你那串子查询
select @n=@@rowcount
while @i<=@n
begin
set @str=(select Voter from #t where id=@i)
insert into tblVOTEWinnerList(.......) values(,,,@str) set @i=@i+1
end
declare @n int
declare @str nvarchar(50)
set @i=1select top 3 Voter into #t from tblVOTEVoteRecord a left join ......--你那串子查询
select @n=@@rowcount
while @i<=@n
begin
set @str=(select Voter from #t where id=@i)
insert into tblVOTEWinnerList(.......) values(,,,@str) set @i=@i+1
end
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
SELECT '111' ,
222 ,
'333' ,
'444' ,
voter AS PhoneNumber
FROM ( SELECT TOP 3
Voter
FROM tblVOTEVoteRecord a
LEFT JOIN tblVOTEActivityInfo b ON a.ActivityID = b.ActivityID
WHERE a.ActivityID = '43bf7430dbae435ca0c1f4856eb8cefa'
AND DrawingID = '222'
ORDER BY NEWID()
) a
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
( select top 3 '111',222,'333','444',PhoneNumber = Voter
from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' order by newid())
from tblVOTEDrawingInfo where DrawingID=‘222’
([ID]
,[DrawingID]
,[ActivityID]
,[RulesID]
,[PhoneNumber]
)
select top 3 '111',222,'333','444',PhoneNumber = Voter
from tblVOTEVoteRecord a left join
tblVOTEActivityInfo b on a.ActivityID=b.ActivityID,tblVOTEDrawingInfo c
where a.ActivityID='43bf7430dbae435ca0c1f4856eb8cefa' and c.DrawingID=‘222’ order by newid()