日期:2014-05-18  浏览次数:20486 次

SQL分组查询问题,急
ALTER proc [dbo].[proc_getZhanDianShuJuData1]
 
  @stime varchar(200),--时间从
  @etime varchar(200),--至
  @start1 varchar(200),
  @end1 varchar(200)
as


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

select @dian1=sum(UsePower) from History where Testtime>=@stime and Testtime<=@etime group by StationName
select @dian2=sum(UsePower) from History where Testtime>=@start1 and Testtime<=@end1 group by StationName
select @liuliang1=sum(Alltotalrunning) from History where Testtime>=@stime and Testtime<=@etime group by StationName
select @liuliang2=sum(Alltotalrunning) from History where Testtime>=@start1 and Testtime<=@end1 group by StationName
select distinct Stationinfo.StationName as 站名,@dian1 as 起始电量,@dian2 as 终止电量,@liuliang1 as 起始累计流量,@liuliang2 as 终止累计流量,
@dian2-@dian1 as 用电量,@liuliang2-@liuliang1 as 用水量,Convert(decimal(18,2),(@dian2-@dian1)/(@liuliang2-@liuliang1)) as 吨水耗电 from History,Stationinfo
where Testtime between @stime and @end1 and History.StationName=Stationinfo.Agreement 


这样写查询出来的数据是重复的。
一店 1193989.00 1937564.00 563617.07 783854.06 743575.00 220236.99 3.38
二店 1193989.00 1937564.00 563617.07 783854.06 743575.00 220236.99 3.38



------解决方案--------------------
重复的时候看下是不是在有个字段的判断有重复的没有注意到。。
------解决方案--------------------
select @dian1=sum(UsePower) from History where Testtime>=@stime and Testtime<=@etime and StationName = '一店' group by StationName

------解决方案--------------------
如果你里面有多个店的数据,group by 出来不是有多条数据吗?不这样指定,你怎么赋值给你的变量?