编写SQL Server函数，返回给定时间段 耗电量 总和
0
悬赏园豆：15
[已解决问题]
解决于 2013-05-07 10:46
CREATE TABLE [dbo].[Powerinfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NULL, [PowerMark] [int] NULL,
[Created] [datetime] NULL, CONSTRAINT [PK_Powerinfo] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Powerinfo', @level2type=N'COLUMN',@level2name=N'Id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Powerinfo', @level2type=N'COLUMN',@level2name=N'UserId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'耗电量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Powerinfo', @level2type=N'COLUMN',@level2name=N'PowerMark' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生成时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Powerinfo', @level2type=N'COLUMN',@level2name=N'Created'
表结构如上所示。 编写一个sql 函数返回 给定用户，在某一时间段内的耗电量 (耗电量=累计耗电量 - 前一天累计耗电量) 烦请高手指点迷津
最佳答案
0
其他回答(1)
0
SELECT SUM(Powermark) - SUM(CASE WHEN Created < '2012-1-4'/*时间段的开始时间*/ THEN Powermark ELSE 0 END) AS TotalMark FROM Powerinfo WHERE UserId = 1 AND Created >= '2012-1-4'/*时间段的开始时间*/ AND Created <= '2012-1-6'/*时间段的结束时间*/
收获园豆：5
insert into Powerinfo values(1,3,'2013-04-22 10:07:16.000') insert into Powerinfo values(2,8,'2013-04-26 10:08:14.967') insert into Powerinfo values(1,12,'2013-04-23 10:08:14.000') insert into Powerinfo values(3,5,'2013-04-26 10:08:14.967') insert into Powerinfo values(1,32,'2013-04-24 10:08:14.000') insert into Powerinfo values(1,45,'2013-04-25 10:08:14.000')
插入一些测试数据，发现结果是错误的。
@菜鸟vs大侠: 哦。多写了一句。把这个
AND Created >= '2012-1-4'/*时间段的开始时间*/
去掉。
@窍雨: 去掉了也不行。你这个语句有问题。我通过一个游标循环已经解决这个问题。
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date, ,> -- Description: <Description, ,> -- ============================================= create FUNCTION getprepowermark_1 ( @user_id int, @begintime datetime, @endtime datetime ) RETURNS int AS BEGIN DECLARE @Power_Mark int, @Id int, @UserId int, @PowerMark int, @CurPowerUsed int, @lastpowerused int, @Created datetime, @tot int set @tot=0 DECLARE authors_cursor_1 CURSOR FOR SELECT Id, UserId,PowerMark,CurPowerUsed,Created FROM Powerinfo where UserId=@user_id and created between @begintime and @endtime OPEN authors_cursor_1 FETCH NEXT FROM authors_cursor_1 INTO @Id, @UserId,@PowerMark,@CurPowerUsed,@Created WHILE @@FETCH_STATUS = 0 begin select @Power_Mark=PowerMark from powerinfo where datediff(day,@Created,Created)=-1 and UserId=@UserId if(@Power_Mark is not null) begin set @tot=@tot+@PowerMark-isnull(@Power_Mark,0) set @Power_Mark=null end FETCH NEXT FROM authors_cursor_1 INTO @Id, @UserId,@PowerMark,@CurPowerUsed,@Created end CLOSE authors_cursor_1 DEALLOCATE authors_cursor_1 RETURN @tot END GO
执行 select dbo.getprepowermark_1(1,'2013-4-20 00:00:01','2013-4-24 23:59:59')
只是游标效率比较差，暂时没想到好的解决方案。有更好的解决方案烦请告知。不甚感激！
