sql时段累计之和问题

net_xiaojian 2010-08-01 07:50:19

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
...全文
224 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
hokor 2010-08-01
  • 打赏
  • 举报
回复
终于理解了,开始完全怪沟里去了。。
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
net_xiaojian 2010-08-01
  • 打赏
  • 举报
回复
P19+P20...P0

时间是从2010-07-13 19点到2010-07-14 的 0点,
东那个升 2010-08-01
  • 打赏
  • 举报
回复
set @begin='2010-7-13 19:18:39' set @end='2010-7-14 0:18:59'

这样呢
net_xiaojian 2010-08-01
  • 打赏
  • 举报
回复
p0+...P18+p19
东那个升 2010-08-01
  • 打赏
  • 举报
回复
set @begin='2010-7-13 0:18:39' set @end='2010-7-14 19:18:59'


这个时间段算哪个

p0+...P18+p19么

还是
P19+P20...P0
SQLCenter 2010-08-01
  • 打赏
  • 举报
回复
我还是不看了,拿那边一哥们的和你自己的代码结合一下
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]
hokor 2010-08-01
  • 打赏
  • 举报
回复
没贴好。。。重贴一下
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
*/
hokor 2010-08-01
  • 打赏
  • 举报
回复
[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]
net_xiaojian 2010-08-01
  • 打赏
  • 举报
回复
ls的做法看得费劲。
hokor 2010-08-01
  • 打赏
  • 举报
回复
提供一种方法,但是比较费劲。。
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
SQLCenter 2010-08-01
  • 打赏
  • 举报
回复
你想干的是什么

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧