sql时段累计之和问题

[net_xiaojian]

create table tb1(id int ,TM datetime,
    p8 int,
    p9 int,
    p10 int,
    p11 int,
    p12 int,
    p13 int,
    p14 int,
    p15 int,
    p16 int,
    p17 int,
    p18 int,
    p19 int,
    p20 int,
    p21 int,
    p22 int,
    p23 int,
    p0 int,
    p1 int,
    p2 int,
    p3 int,
    p4 int,
    p5 int,
    p6 int,
    p7 int
    )

p类似的字段代表小时。

insert into @table
select 118,'2010-07-14 01:00:00',1001,2002,3003,4004,5005,6006,7007,8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024
union all
select 119,'2010-07-10 、10:00:00',1001,2002,3003,4004,5005,6006,7007,8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024

界面上时间选择：
1、2010-07-01 19     ---    2010-07-15 01，p19+p20+...+p0+p1（时间段 TM between '2010-07-01' and '2010-07-15' 时间段不用管，主要是时段相加不懂）
2、2010-07-01 01     ---    2010-07-15 07，p1+...+p7  这种好做点。

--数据显示
id,           累计之和

http://topic.csdn.net/u/20100714/12/daac12b8-035f-4938-a891-110b7c6868e2.html

[SQLCenter]

你想干的是什么

[hokor]

提供一种方法，但是比较费劲。。

create table tb (id int ,TM datetime,
    p8 int,
    p9 int,
    p10 int,
    p11 int,
    p12 int,
    p13 int,
    p14 int,
    p15 int,
    p16 int,
    p17 int,
    p18 int,
    p19 int,
    p20 int,
    p21 int,
    p22 int,
    p23 int,
    p0 int,
    p1 int,
    p2 int,
    p3 int,
    p4 int,
    p5 int,
    p6 int,
    p7 int
    )
insert into tb
select 118,'2010-07-14 01:00:00',1001,2002,3003,4004,5005,6006,7007,8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024
union all
select 119,'2010-07-10 10:00:00',1001,2002,3003,4004,5005,6006,7007,8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024

if object_id('tempdb..#tb') is not null
drop table tb 

SELECT 8 num,1 sort,id,TM,p8 P into #TB FROM TB 
UNION ALL SELECT 9 num,2 sort,id,TM,p9 FROM TB
UNION ALL SELECT 10 num,3 sort,id,TM,p10 FROM TB
UNION ALL SELECT 11 num,4 sort,id,TM,p11 FROM TB
UNION ALL SELECT 12 num,5 sort,id,TM,p12 FROM TB
UNION ALL SELECT 13 num,6 sort,id,TM,p13 FROM TB
UNION ALL SELECT 14 num,7 sort,id,TM,p14 FROM TB
UNION ALL SELECT 15 num,8 sort,id,TM,p15 FROM TB
UNION ALL SELECT 16 num,9 sort,id,TM,p16 FROM TB
UNION ALL SELECT 17 num,10 sort,id,TM,p17 FROM TB
UNION ALL SELECT 18 num,11 sort,id,TM,p18 FROM TB
UNION ALL SELECT 19 num,12 sort,id,TM,p19 FROM TB
UNION ALL SELECT 20 num,13 sort,id,TM,p20 FROM TB
UNION ALL SELECT 21 num,14 sort,id,TM,p21 FROM TB
UNION ALL SELECT 22 num,15 sort,id,TM,p22 FROM TB
UNION ALL SELECT 23 num,16 sort,id,TM,p23 FROM TB
UNION ALL SELECT 0 num,17 sort,id,TM,p0  FROM TB
UNION ALL SELECT 1 num,18 sort,id,TM,p1  FROM TB
UNION ALL SELECT 2 num,19 sort,id,TM,p2  FROM TB
UNION ALL SELECT 3 num,20 sort,id,TM,p3  FROM TB
UNION ALL SELECT 4 num,21 sort,id,TM,p4  FROM TB
UNION ALL SELECT 5 num,22 sort,id,TM,p5  FROM TB
UNION ALL SELECT 6 num,23 sort,id,TM,p6  FROM TB
UNION ALL SELECT 7 num,24 sort,id,TM,p7  FROM TB

SELECT id,tm,sum(p) from #TB t where convert(varchar(10),tm,120) = '2010-07-10'  and sort > (select top 1 sort from #tb where num = 19)
GROUP BY id,tm

[net_xiaojian]

ls的做法看得费劲。

[hokor]

[Quote=引用 2 楼 hokor 的回复:]
提供一种方法，但是比较费劲。。

SQL code
create table tb (id int ,TM datetime,
p8 int,
p9 int,
p10 int,
p11 int,
p12 int,
p13 int,
p14 int,
p15 int,
p16 int,
p17 ……
[/Quote] 改一下应该是sort >=

SELECT id,tm,sum(p) from #TB t where convert(varchar(10),tm,120) = '2010-07-10'  and sort >= (select top 1 sort from #tb where num = 19)
在提供一种动态SQL 的方法前面已经有人写过了。
[code=SQL]DECLARE @TM varchar(10),@n int
SELECT @TM = '2010-07-10',@n = 19 
DECLARE @SQL varchar(max)
SELECT @SQL = ISNULL(@SQL+'+','SELECT id,')+ QUOTENAME(name) FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('tb') AND column_id >= (SELECT  column_id FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('tb') and name = 'P'+convert(varchar,@n))
SELECT @SQL = @SQL + 'AS 累计之和'+char(13)+'FROM TB WHERE convert(varchar(10),tm,120) = '''+@TM+''''
PRINT @SQL
EXEC(@SQL)
/*
SELECT id,TM,[p19]+[p20]+[p21]+[p22]+[p23]+[p0]+[p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]AS 累计之和
FROM TB WHERE convert(varchar(10),tm,120) = '2010-07-10'
id	 累计之和
119  2340234
*/

GROUP BY id,tm[/code]

[hokor]

没贴好。。。重贴一下

DECLARE @TM varchar(10),@n int
SELECT @TM = '2010-07-10',@n = 19 
DECLARE @SQL varchar(max)
SELECT @SQL = ISNULL(@SQL+'+','SELECT id,')+ QUOTENAME(name) FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('tb') AND column_id >= (SELECT  column_id FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('tb') and name = 'P'+convert(varchar,@n))
SELECT @SQL = @SQL + 'AS 累计之和'+char(13)+'FROM TB WHERE convert(varchar(10),tm,120) = '''+@TM+''''
PRINT @SQL
EXEC(@SQL)
/*
SELECT id,TM,[p19]+[p20]+[p21]+[p22]+[p23]+[p0]+[p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]AS 累计之和
FROM TB WHERE convert(varchar(10),tm,120) = '2010-07-10'
id	 累计之和
119  2340234
*/

[SQLCenter]

我还是不看了，拿那边一哥们的和你自己的代码结合一下

declare @begin datetime                        --开始时间
declare @end  datetime                --结束时间
declare @num float
declare @num1 float
declare @h int                         --小时
declare @strsql nvarchar(4000)

set @begin='2010-7-13 21:18:39' set @end='2010-7-14 11:18:59'
--需要判断时间 输入是否准确

--将日期内的数据求和
select @num = sum(isnull(p8,0))+sum(isnull(p9,0))+sum(isnull(p10,0))+sum(isnull(p11,0))+sum(isnull(p12,0))+sum(isnull(p13,0))+sum(isnull(p14,0))+
sum(isnull(p15,0))+sum(isnull(p16,0))+sum(isnull(p17,0))+sum(isnull(p18,0))+sum(isnull(p19,0))+sum(isnull(p20,0))+sum(isnull(p21,0))+sum(isnull(p2,0))+
sum(isnull(p23,0))+sum(isnull(p0,0))+sum(isnull(p1,0))+sum(isnull(p2,0))+sum(isnull(p3,0))+sum(isnull(p4,0))+sum(isnull(p5,0))+sum(isnull(p6,0))+sum(isnull(p7,0)) 
from st_rain_s where tm between convert(varchar(10),@begin,120) and convert(varchar(10),@end,120)

--截头 将0点到 20点的 求和减掉
select @strsql = null, @h=datepart(hh,@begin)-1
while @h>=0
begin
	set @strsql = isnull(@strsql+'+','') + 'p' + ltrim(@h)
	set @h = @h - 1
end
set @strsql = 'select @num1 = ' + @strsql + ' from st_rain_s where tm = ' + convert(varchar(10),@begin,120) + ''''
exec sp_executesql @strsql, N'@num1 float output', @num1 output
set @num = @num - @num1

--截头 将12点到 23点的 求和减掉
select @strsql = null, @h=datepart(hh,@end)+1
while @h < 24
begin
	set @strsql = isnull(@strsql+'+','') + 'p' + ltrim(@h)
	set @h = @h + 1
end
set @strsql = 'select @num1 = ' + @strsql + ' from st_rain_s where tm = ' + convert(varchar(10),@end,120) + ''''
exec sp_executesql @strsql, N'@num1 float output', @num1 output
set @num = @num - @num1

select @num[code=SQL]

[/code]

[东那个升]

set @begin='2010-7-13 0:18:39' set @end='2010-7-14 19:18:59'


这个时间段算哪个

p0+...P18+p19么

还是
P19+P20...P0

[net_xiaojian]

p0+...P18+p19

[东那个升]

set @begin='2010-7-13 19:18:39' set @end='2010-7-14 0:18:59'

这样呢

[net_xiaojian]

P19+P20...P0

时间是从2010-07-13 19点到2010-07-14 的 0点，

[hokor]

终于理解了，开始完全怪沟里去了。。

declare @begin datetime    --开始时间
declare @end  datetime        --结束时间
set @begin='2010-7-13 21:18:39' set @end='2010-7-14 11:18:59'

SELECT ID,SUM(P)[累计之和] FROM (
SELECT id,TM,p8 P FROM TB 
UNION ALL SELECT id,TM,p9 FROM TB
UNION ALL SELECT id,TM,p10 FROM TB
UNION ALL SELECT id,TM,p11 FROM TB
UNION ALL SELECT id,TM,p12 FROM TB
UNION ALL SELECT id,TM,p13 FROM TB
UNION ALL SELECT id,TM,p14 FROM TB
UNION ALL SELECT id,TM,p15 FROM TB
UNION ALL SELECT id,TM,p16 FROM TB
UNION ALL SELECT id,TM,p17 FROM TB
UNION ALL SELECT id,TM,p18 FROM TB
UNION ALL SELECT id,TM,p19 FROM TB
UNION ALL SELECT id,TM,p20 FROM TB
UNION ALL SELECT id,TM,p21 FROM TB
UNION ALL SELECT id,TM,p22 FROM TB
UNION ALL SELECT id,TM,p23 FROM TB
UNION ALL SELECT id,TM,p0 FROM TB
UNION ALL SELECT id,TM,p1 FROM TB
UNION ALL SELECT id,TM,p2 FROM TB
UNION ALL SELECT id,TM,p3 FROM TB
UNION ALL SELECT id,TM,p4 FROM TB
UNION ALL SELECT id,TM,p5 FROM TB
UNION ALL SELECT id,TM,p6 FROM TB
UNION ALL SELECT id,TM,p7 FROM TB
) T 
WHERE dateadd(hh,datediff(hh,0,TM),0) 
between dateadd(hh,datediff(hh,0,@begin),0) 
    and dateadd(hh,datediff(hh,0,@end),0)
GROUP BY ID