首页 新闻 搜索 专区 学院

编写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 函数返回 给定用户,在某一时间段内的耗电量  (耗电量=累计耗电量 - 前一天累计耗电量)  烦请高手指点迷津

菜鸟vs大侠的主页 菜鸟vs大侠 | 初学一级 | 园豆:62
提问于:2013-04-26 09:18
< >
分享
最佳答案
0
收获园豆:10
邀月 | 高人七级 |园豆:25475 | 2013-05-02 15:43
其他回答(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
流浪命 | 园豆:28 (初学一级) | 2013-04-26 10:53
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')

插入一些测试数据,发现结果是错误的。

支持(0) 反对(0) 菜鸟vs大侠 | 园豆:62 (初学一级) | 2013-04-26 11:14

@菜鸟vs大侠: 哦。多写了一句。把这个

AND Created >= '2012-1-4'/*时间段的开始时间*/

去掉。

支持(0) 反对(0) 流浪命 | 园豆:28 (初学一级) | 2013-04-26 11:37

@窍雨:  去掉了也不行。你这个语句有问题。我通过一个游标循环已经解决这个问题。

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')

只是游标效率比较差,暂时没想到好的解决方案。有更好的解决方案烦请告知。不甚感激!

支持(0) 反对(0) 菜鸟vs大侠 | 园豆:62 (初学一级) | 2013-04-26 12:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册