SQL subqueries errors, anxious! !

Category: MS-SQL Server -> Applications Author: lq27287387 Date: 2014-06-01 14:49:05
 
lq27287387
2014-06-01 14:49:05
ALTER proc [dbo]. [Proc_getZhanDianShuJuData1]

@ stime varchar (200), - start time from
@ etime varchar (200), - to
@ start1 varchar (200) - Termination Time from,
@ end1 varchar (200) - to
as

declare @ dian1 decimal (18,2), @ dian2 decimal (18,2), @ liuliang1 decimal (18,2), @ liuliang2 decimal (18,2 )

set @ dian1 = (select sum (UsePower) from History where Testtime> = @ stime and Testtime <= @ etime group by StationName)
set @ dian2 = (select sum (UsePower) from History where Testtime> = @ start1 and Testtime <= @ end1 group by StationName)
set ; @ liuliang1 = (select sum (Alltotalrunning) from History where Testtime> = @ stime and Testtime <= @ etime group by StationName)
set @ liuliang2 = (select ; sum (Alltotalrunning) from History where Testtime> = @ start1 and Testtime <= @ end1 group by StationName)
select distinct Stationinfo.StationName as station name, @ dian1 as the starting power, @ dian2 as termination charge, @ liuliang1 as the starting cumulative flow, @ liuliang2 as termination cumulative flow,
@ [email protected] dian1 as electricity volume, @ [email protected] liuliang1 as water, Convert (decimal (18,2), (@ [email protected] dian1) / (@ [email protected] liuliang1)) as tons of water consumption from History, Stationinfo
where Testtime between @ stime and @ end1 and History.StationName = Stationinfo.Agreement group by Stationinfo.Agreement, Stationinfo.StationName

The above is what I wrote a stored procedure, I have added in a subquery group by not grouped query data. Then error: Subquery returned more than 1 value. When the subquery follows =,! =, <, <=,>,> = Or when the subquery is used as an expression, this situation is not allowed.

GreatAwei
2014-06-01 14:51:34
set @dian1=(select sum(UsePower) from History where Testtime>[email protected] and Testtime<[email protected] group by StationName )


wording such errors occur when there are multiple StationName error

example: the cycle assignment
select  @dian1=sum(UsePower) from History where Testtime>[email protected] and Testtime<[email protected] group by StationName
zgsu27
2014-06-01 15:01:19
set @ dian1 = (select sum (UsePower) from History ; where Testtime> = @ stime and Testtime <= @ etime group by StationName)
set @ dian2 = (select sum (UsePower) from History where Testtime> = @ start1 and Testtime <= @ end1 group by StationName)
set @ liuliang1 = (select sum (Alltotalrunning) from History where Testtime> = @ stime and ; Testtime <= @ etime group by StationName)
set @ liuliang2 = (select sum (Alltotalrunning) from History where Testtime> = @ start1 and Testtime <= @ end1 ; group by StationName)

brackets group by grouping subquery returns multiple rows, set statement does not know which line you want to sum the result to the variable assignment, so wrong
bibicc
2014-06-01 15:16:43
So you want to ensure that a given variable assignment with subqueries Subqueries can only return when the row
zonglinlin
2014-06-01 15:35:27
Subquery returned more than 1 value. When the subquery follows =,! =, <, <=,>,> =

So subqueries may be multiple values ​​can generally be The IN solve
rr125wxf
2014-06-01 15:48:50
select @ dian1 = sum (UsePower) from History where Testtime> = @ stime and Testtime <= @ etime group by StationName

so you can check out, but check out after the data is the same. Doesn grouped.
joyce_yee
2014-06-01 16:05:37
general use IN to resolve. If you want to group, then make a recommendation LZ view and then in the final group by