使用SQL进行计算

[风影枫林]

现在要将table1中的数据转到table2中,请教SQL语句该如何实现(只需要查询语句就行了);
将table1中2条 name 和 time 都相同的数据整合为table2的一条数据

--假设现在时间为11点过5分 2011-6-13 11:05:00 
--2011-6-13 11:00:00 的value3的值为
--2011-6-13 11:00:00 时间点的value减去2011-6-13 10:00:00时间点的value


--假设现在时间为12点过5分   2011-6-13 12:05:00 
--value3的值是当type=2时,2011-6-13 12:00:00的value3的值为
--2011-6-13 12:00:00 时间点的value减去2011-6-13 11:00:00时间点的value

--依次类推    

--特殊情况,如果当前时间为早上8点过5分    2011-6-13 08:05:00 
--value3的值是当type=2时,2011-6-13 08:00:00的value3的值为
--2011-6-13 09:00:00 时间点的value    


create table table1(name varchar(10),time datetime,value int,type int)

insert table1
--name        time                   value        type
select 'A','2011-6-13 11:00:00',         51      ,     1 union all
select 'B','2011-6-13 12:00:00',         35       ,    1 union all
select 'C','2011-6-13 11:00:00',         43        ,   1 union all
select 'B','2011-6-13 12:00:00',         8          ,  2 union all
select 'A','2011-6-13 11:00:00',         8         ,   2 union all
select 'C','2011-6-13 12:00:00',         10         ,  2 union all
select 'C','2011-6-13 11:00:00',         7           , 2 union all
select 'A','2011-6-13 12:00:00',         9            ,2 union all
select 'A','2011-6-13 12:00:00',         54           ,1 union all
select 'B','2011-6-13 11:00:00',         3            ,2 union all
select 'B','2011-6-13 11:00:00',         38           ,1 union all
select 'A','2011-6-13 10:00:00',         6           , 2 union all
select 'A','2011-6-13  9:00:00',         3           , 2 union all
select 'A','2011-6-13  8:00:00',         4           , 2 union all
select 'A','2011-6-13  7:00:00',         3           , 2 



--这是在table2中没有字段value3时的SQL语句
select name,convert(varchar(10),[time],120) as [time],
sum(case when type=1 then value else 0 end) as value1,
sum(case when type=2 then value else 0 end) as value2
from table1
group by name,convert(varchar(10),[time],120)

表 table2
name      time                    value1            value2            value3

[-晴天]

是这样么?

create table table1(name varchar(10),time datetime,value int,type int)

insert table1
--name        time                   value        type
select 'A','2011-6-13 11:00:00',         51      ,     1 union all
select 'B','2011-6-13 12:00:00',         35       ,    1 union all
select 'C','2011-6-13 11:00:00',         43        ,   1 union all
select 'B','2011-6-13 12:00:00',         8          ,  2 union all
select 'A','2011-6-13 11:00:00',         8         ,   2 union all
select 'C','2011-6-13 12:00:00',         10         ,  2 union all
select 'C','2011-6-13 11:00:00',         7           , 2 union all
select 'A','2011-6-13 12:00:00',         9            ,2 union all
select 'A','2011-6-13 12:00:00',         54           ,1 union all
select 'B','2011-6-13 11:00:00',         3            ,2 union all
select 'B','2011-6-13 11:00:00',         38           ,1 union all
select 'A','2011-6-13 10:00:00',         6           , 2 union all
select 'A','2011-6-13  9:00:00',         3           , 2 union all
select 'A','2011-6-13  8:00:00',         4           , 2 union all
select 'A','2011-6-13  7:00:00',         3           , 2 
go
;with c1 as(
select * from table1
union all
select name,time,0 as value,(case when type=1 then 2 else 1 end)type from(
select * from table1 a where not exists(select 1 from table1 where name=a.name and time=a.time and type<>a.type)
)t
),c2 as(
select a.name,a.time,a.value v1,b.value v2
from c1 a inner join c1 b on a.name=b.name and a.time=b.time and a.type=b.type-1
)
,c3 as(
select a.name,a.time,(case when convert(varchar(2),a.time,8)='08' then (select top 1 value from table1 where name=a.name and time=dateadd(hh,1,a.time) and type=2)else a.value-b.value end)v3 from table1 a inner join table1 b on a.name=b.name and datediff(hh,b.time,a.time)=1-- and a.type=2 and b.type=2
where a.type=2 and b.type=2 --order by a.name,a.time
)
select a.name,a.time,a.v1,a.v2,isnull(b.v3,0)v3 from c2 a left join c3 b on a.name=b.name and a.time=b.time order by a.name,a.time
go
drop table table1
/*
name       time                    v1          v2          v3
---------- ----------------------- ----------- ----------- -----------
A          2011-06-13 07:00:00.000 0           3           0
A          2011-06-13 08:00:00.000 0           4           3
A          2011-06-13 09:00:00.000 0           3           -1
A          2011-06-13 10:00:00.000 0           6           3
A          2011-06-13 11:00:00.000 51          8           2
A          2011-06-13 12:00:00.000 54          9           1
B          2011-06-13 11:00:00.000 38          3           0
B          2011-06-13 12:00:00.000 35          8           5
C          2011-06-13 11:00:00.000 43          7           0
C          2011-06-13 12:00:00.000 0           10          3

(10 行受影响)

*/

[风影枫林]

查询这些测试数据得到的结果是正确的,不过这些SQL语句太长,有很多地方我都看不懂,不知道换成真实的数据是不是正确的, 晚上再用真实数据测试

[-晴天]

[code=SQLinsert] into st_rtsr_r select '06600','2011-06-03 09:00:00.000',1,50.07,11,41,0,'2011-06-03 08:58:12.000','2011-06-03 00:58:12.000','2011-06-03 00:58:12.000'
insert into st_rtsr_r select '07210','2011-06-03 09:00:00.000',1,16.89,11,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 09:00:00.000',1,17.01,11,41,0,'2011-06-03 09:58:12.000','2011-06-03 01:58:12.000','2011-06-03 01:58:12.000'
insert into st_rtsr_r select '07220','2011-06-03 09:00:00.000',1,11.97,11,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 09:00:00.000',1,13.3,51,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '07210','2011-06-03 09:00:00.000',1,13.3,51,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '07210','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:13.000','2011-06-03 00:58:13.000','2011-06-03 00:58:13.000'
insert into st_rtsr_r select '28200','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:53.000','2011-06-03 00:58:53.000','2011-06-03 00:58:53.000'
insert into st_rtsr_r select '28600','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:53.000','2011-06-03 00:58:53.000','2011-06-03 00:58:53.000'
insert into st_rtsr_r select '29800','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:53.000','2011-06-03 00:58:53.000','2011-06-03 00:58:53.000'
insert into st_rtsr_r select '30400','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:53.000','2011-06-03 00:58:53.000','2011-06-03 00:58:53.000'
insert into st_rtsr_r select '2DAWA','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:53.000','2011-06-03 00:58:53.000','2011-06-03 00:58:53.000'
insert into st_rtsr_r select '2HOUS','2011-06-03 09:00:00.000',1,0,24,41,0,'2011-06-03 08:58:52.000','2011-06-03 00:58:52.000','2011-06-03 00:58:52.000'
insert into st_rtsr_r select '07000','2011-06-03 09:00:00.000',1,13.4,51,41,0,'2011-06-03 09:58:13.000','2011-06-03 01:58:13.000','2011-06-03 01:58:13.000'
insert into st_rtsr_r select '07220','2011-06-03 08:00:00.000',1,11.96,11,41,0,'2011-06-03 07:58:13.000','2011-06-02 23:58:13.000','2011-06-02 23:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 08:00:00.000',1,13.1,51,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '07210','2011-06-03 08:00:00.000',1,12.9,51,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '28600','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:53.000','2011-06-02 23:58:53.000','2011-06-02 23:58:53.000'
insert into st_rtsr_r select '2DAWA','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:52.000','2011-06-02 23:58:52.000','2011-06-02 23:58:52.000'
insert into st_rtsr_r select '07000','2011-06-03 08:00:00.000',1,17.02,11,41,0,'2011-06-03 07:58:13.000','2011-06-02 23:58:13.000','2011-06-02 23:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 08:00:00.000',1,0,24,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '07210','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '07000','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '06600','2011-06-03 08:00:00.000',1,50.07,11,41,0,'2011-06-03 07:58:12.000','2011-06-02 23:58:12.000','2011-06-02 23:58:12.000'
insert into st_rtsr_r select '07210','2011-06-03 08:00:00.000',1,16.88,11,41,0,'2011-06-03 07:58:13.000','2011-06-02 23:58:13.000','2011-06-02 23:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 08:00:00.000',1,13.1,51,41,0,'2011-06-03 07:58:14.000','2011-06-02 23:58:14.000','2011-06-02 23:58:14.000'
insert into st_rtsr_r select '28200','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:53.000','2011-06-02 23:58:53.000','2011-06-02 23:58:53.000'
insert into st_rtsr_r select '29800','2011-06-03 08:00:00.000',1,0,24,41,0,'2011-06-03 07:58:53.000','2011-06-02 23:58:53.000','2011-06-02 23:58:53.000'
insert into st_rtsr_r select '30400','2011-06-03 08:00:00.000',1,0,24,41,0,'2011-06-03 07:58:53.000','2011-06-02 23:58:53.000','2011-06-02 23:58:53.000'
insert into st_rtsr_r select '2HOUS','2011-06-03 08:00:00.000',1,0.5,24,41,0,'2011-06-03 07:58:53.000','2011-06-02 23:58:53.000','2011-06-02 23:58:53.000'
insert into st_rtsr_r select '07210','2011-06-03 07:00:00.000',1,12.7,51,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 07:00:00.000',1,12.8,51,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '07220','2011-06-03 07:00:00.000',1,11.97,11,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 07:00:00.000',1,12.9,51,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '07210','2011-06-03 07:00:00.000',1,16.9,11,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 07:00:00.000',1,17.02,11,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 07:00:00.000',1,0,24,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '07210','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '28600','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:53.000','2011-06-02 22:58:53.000','2011-06-02 22:58:53.000'
insert into st_rtsr_r select '2DAWA','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:53.000','2011-06-02 22:58:53.000','2011-06-02 22:58:53.000'
insert into st_rtsr_r select '07000','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:13.000','2011-06-02 22:58:13.000','2011-06-02 22:58:13.000'
insert into st_rtsr_r select '28200','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:53.000','2011-06-02 22:58:53.000','2011-06-02 22:58:53.000'
insert into st_rtsr_r select '29800','2011-06-03 07:00:00.000',1,0,24,41,0,'2011-06-03 06:58:53.000','2011-06-02 22:58:53.000','2011-06-02 22:58:53.000'
insert into st_rtsr_r select '30400','2011-06-03 07:00:00.000',1,0,24,41,0,'2011-06-03 06:58:52.000','2011-06-02 22:58:52.000','2011-06-02 22:58:52.000'
insert into st_rtsr_r select '2HOUS','2011-06-03 07:00:00.000',1,0.5,24,41,0,'2011-06-03 06:58:52.000','2011-06-02 22:58:52.000','2011-06-02 22:58:52.000'
insert into st_rtsr_r select '06600','2011-06-03 07:00:00.000',1,50.07,11,41,0,'2011-06-03 06:58:12.000','2011-06-02 22:58:12.000','2011-06-02 22:58:12.000'
insert into st_rtsr_r select '07210','2011-06-03 06:00:00.000',1,16.89,11,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '07220','2011-06-03 06:00:00.000',1,11.97,11,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 06:00:00.000',1,12.8,51,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 06:00:00.000',1,12.7,51,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '07210','2011-06-03 06:00:00.000',1,12.6,51,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '07000','2011-06-03 06:00:00.000',1,17.02,11,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 06:00:00.000',1,50.07,11,41,0,'2011-06-03 05:58:12.000','2011-06-02 21:58:12.000','2011-06-02 21:58:12.000'
insert into st_rtsr_r select '28600','2011-06-03 06:00:00.000',1,0.5,24,41,0,'2011-06-03 05:58:53.000','2011-06-02 21:58:53.000','2011-06-02 21:58:53.000'
insert into st_rtsr_r select '2DAWA','2011-06-03 06:00:00.000',1,0.5,24,41,0,'2011-06-03 05:58:52.000','2011-06-02 21:58:52.000','2011-06-02 21:58:52.000'
insert into st_rtsr_r select '07210','2011-06-03 06:00:00.000',1,0.5,24,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '06600','2011-06-03 06:00:00.000',1,0,24,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '30400','2011-06-03 06:00:00.000',1,0,24,41,0,'2011-06-03 05:58:53.000','2011-06-02 21:58:53.000','2011-06-02 21:58:53.000'
insert into st_rtsr_r select '2HOUS','2011-06-03 06:00:00.000',1,0,24,41,0,'2011-06-03 05:58:52.000','2011-06-02 21:58:52.000','2011-06-02 21:58:52.000'
insert into st_rtsr_r select '29800','2011-06-03 06:00:00.000',1,0,24,41,0,'2011-06-03 05:58:53.000','2011-06-02 21:58:53.000','2011-06-02 21:58:53.000'
insert into st_rtsr_r select '07000','2011-06-03 06:00:00.000',1,0.5,24,41,0,'2011-06-03 05:58:13.000','2011-06-02 21:58:13.000','2011-06-02 21:58:13.000'
insert into st_rtsr_r select '28200','2011-06-03 06:00:00.000',1,0,24,41,0,'2011-06-03 05:58:53.000','2011-06-02 21:58:53.000','2011-06-02 21:58:53.000'
go[/code

[风影枫林]

为什么我执行查询语句的时候一直都显示正在执行查询阿?
一直都没有执行结果?

[风影枫林]

上面的语句我在sql server 2008里可以正常执行.
但在2000里好象就不能使用了.

2000里是不是不能用with 关键字啊?

[-晴天]

这样,就可以在2000里运行了:

--;with c1 as(
select * into #1 from(
select stcd,ymdhm,deval,valtyp from st_rtsr_r
union all
select stcd,ymdhm,0 as deval,(case when valtyp=11 then 24 else 1 end)valtyp from(
select stcd,ymdhm,deval,valtyp from st_rtsr_r a where not exists(select 1 from st_rtsr_r where stcd=a.stcd and ymdhm=a.ymdhm and valtyp<>a.valtyp)
)t)t1
--),c2 as(
select a.stcd,a.ymdhm,a.deval v1,b.deval v2 into #2
from #1 a inner join #1 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm and a.valtyp=b.valtyp-13
--),c3 as(
select a.stcd,a.ymdhm,(case when convert(varchar(2),a.ymdhm,8)='08' then (select top 1 deval from st_rtsr_r where stcd=a.stcd and ymdhm=dateadd(hh,1,a.ymdhm) and valtyp=24)else a.deval-b.deval end)v3 
into #3
from st_rtsr_r a inner join st_rtsr_r b on a.stcd=b.stcd and datediff(hh,b.ymdhm,a.ymdhm)=1-- and a.valtyp=24 and b.valtyp=24
where a.valtyp=24 and b.valtyp=24 --order by a.stcd,a.ymdhm

select a.stcd,a.ymdhm,a.v1,a.v2,isnull(b.v3,0)v3 from #2 a left join #3 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm order by a.stcd,a.ymdhm
go
drop table st_rtsr_r,#1,#2,#3

[风影枫林]

我把上面的SQL语句改成真实的数据后得到的结果总是不正确,晴天能帮忙看一下吗?
表名st_rtsr_r代替原来的table1
表结构如下

--stcd对应name
--ymdhm对应time
--deval对应value
--valtyp对应type(11对应1,24对应2)
--其他字段暂时用不着
CREATE TABLE [dbo].[st_rtsr_r](
	[stcd] [char](8) NOT NULL,
	[ymdhm] [datetime] NOT NULL,
	[devno] [int] NOT NULL,
	[deval] [float] NOT NULL,
	[valtyp] [int] NOT NULL,
	[channel] [int] NOT NULL,
	[flag] [char](1) NULL,
	[inserttime] [datetime] NULL,
	[lasteditdate] [datetime] NULL,
	[creationdate] [datetime] NULL
) ON [PRIMARY]

GO

[风影枫林]

--另外,table2增加一个字段value4，当valtyp=51的时候值为value4的值
--下面是st_rtsr_r表的数据

61606600	2011-06-03 09:00:00.000	1	50.07	11	41	0	2011-06-03 08:58:12.000	2011-06-03 00:58:12.000	2011-06-03 00:58:12.000
61607210	2011-06-03 09:00:00.000	1	16.89	11	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61607000	2011-06-03 09:00:00.000	1	17.01	11	41	0	2011-06-03 09:58:12.000	2011-06-03 01:58:12.000	2011-06-03 01:58:12.000
61607220	2011-06-03 09:00:00.000	1	11.97	11	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61606600	2011-06-03 09:00:00.000	1	13.3	51	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61607210	2011-06-03 09:00:00.000	1	13.3	51	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61606600	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61607210	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:13.000	2011-06-03 00:58:13.000	2011-06-03 00:58:13.000
61628200	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:53.000	2011-06-03 00:58:53.000	2011-06-03 00:58:53.000
61628600	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:53.000	2011-06-03 00:58:53.000	2011-06-03 00:58:53.000
61629800	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:53.000	2011-06-03 00:58:53.000	2011-06-03 00:58:53.000
61630400	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:53.000	2011-06-03 00:58:53.000	2011-06-03 00:58:53.000
6162DAWA	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:53.000	2011-06-03 00:58:53.000	2011-06-03 00:58:53.000
6162HOUS	2011-06-03 09:00:00.000	1	0	24	41	0	2011-06-03 08:58:52.000	2011-06-03 00:58:52.000	2011-06-03 00:58:52.000
61607000	2011-06-03 09:00:00.000	1	13.4	51	41	0	2011-06-03 09:58:13.000	2011-06-03 01:58:13.000	2011-06-03 01:58:13.000
61607220	2011-06-03 08:00:00.000	1	11.96	11	41	0	2011-06-03 07:58:13.000	2011-06-02 23:58:13.000	2011-06-02 23:58:13.000
61606600	2011-06-03 08:00:00.000	1	13.1	51	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61607210	2011-06-03 08:00:00.000	1	12.9	51	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61628600	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:53.000	2011-06-02 23:58:53.000	2011-06-02 23:58:53.000
6162DAWA	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:52.000	2011-06-02 23:58:52.000	2011-06-02 23:58:52.000
61607000	2011-06-03 08:00:00.000	1	17.02	11	41	0	2011-06-03 07:58:13.000	2011-06-02 23:58:13.000	2011-06-02 23:58:13.000
61606600	2011-06-03 08:00:00.000	1	0	24	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61607210	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61607000	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61606600	2011-06-03 08:00:00.000	1	50.07	11	41	0	2011-06-03 07:58:12.000	2011-06-02 23:58:12.000	2011-06-02 23:58:12.000
61607210	2011-06-03 08:00:00.000	1	16.88	11	41	0	2011-06-03 07:58:13.000	2011-06-02 23:58:13.000	2011-06-02 23:58:13.000
61607000	2011-06-03 08:00:00.000	1	13.1	51	41	0	2011-06-03 07:58:14.000	2011-06-02 23:58:14.000	2011-06-02 23:58:14.000
61628200	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:53.000	2011-06-02 23:58:53.000	2011-06-02 23:58:53.000
61629800	2011-06-03 08:00:00.000	1	0	24	41	0	2011-06-03 07:58:53.000	2011-06-02 23:58:53.000	2011-06-02 23:58:53.000
61630400	2011-06-03 08:00:00.000	1	0	24	41	0	2011-06-03 07:58:53.000	2011-06-02 23:58:53.000	2011-06-02 23:58:53.000
6162HOUS	2011-06-03 08:00:00.000	1	0.5	24	41	0	2011-06-03 07:58:53.000	2011-06-02 23:58:53.000	2011-06-02 23:58:53.000
61607210	2011-06-03 07:00:00.000	1	12.7	51	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61607000	2011-06-03 07:00:00.000	1	12.8	51	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61607220	2011-06-03 07:00:00.000	1	11.97	11	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61606600	2011-06-03 07:00:00.000	1	12.9	51	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61607210	2011-06-03 07:00:00.000	1	16.9	11	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61607000	2011-06-03 07:00:00.000	1	17.02	11	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61606600	2011-06-03 07:00:00.000	1	0	24	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61607210	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61628600	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:53.000	2011-06-02 22:58:53.000	2011-06-02 22:58:53.000
6162DAWA	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:53.000	2011-06-02 22:58:53.000	2011-06-02 22:58:53.000
61607000	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:13.000	2011-06-02 22:58:13.000	2011-06-02 22:58:13.000
61628200	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:53.000	2011-06-02 22:58:53.000	2011-06-02 22:58:53.000
61629800	2011-06-03 07:00:00.000	1	0	24	41	0	2011-06-03 06:58:53.000	2011-06-02 22:58:53.000	2011-06-02 22:58:53.000
61630400	2011-06-03 07:00:00.000	1	0	24	41	0	2011-06-03 06:58:52.000	2011-06-02 22:58:52.000	2011-06-02 22:58:52.000
6162HOUS	2011-06-03 07:00:00.000	1	0.5	24	41	0	2011-06-03 06:58:52.000	2011-06-02 22:58:52.000	2011-06-02 22:58:52.000
61606600	2011-06-03 07:00:00.000	1	50.07	11	41	0	2011-06-03 06:58:12.000	2011-06-02 22:58:12.000	2011-06-02 22:58:12.000
61607210	2011-06-03 06:00:00.000	1	16.89	11	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61607220	2011-06-03 06:00:00.000	1	11.97	11	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61606600	2011-06-03 06:00:00.000	1	12.8	51	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61607000	2011-06-03 06:00:00.000	1	12.7	51	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61607210	2011-06-03 06:00:00.000	1	12.6	51	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61607000	2011-06-03 06:00:00.000	1	17.02	11	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61606600	2011-06-03 06:00:00.000	1	50.07	11	41	0	2011-06-03 05:58:12.000	2011-06-02 21:58:12.000	2011-06-02 21:58:12.000
61628600	2011-06-03 06:00:00.000	1	0.5	24	41	0	2011-06-03 05:58:53.000	2011-06-02 21:58:53.000	2011-06-02 21:58:53.000
6162DAWA	2011-06-03 06:00:00.000	1	0.5	24	41	0	2011-06-03 05:58:52.000	2011-06-02 21:58:52.000	2011-06-02 21:58:52.000
61607210	2011-06-03 06:00:00.000	1	0.5	24	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61606600	2011-06-03 06:00:00.000	1	0	24	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61630400	2011-06-03 06:00:00.000	1	0	24	41	0	2011-06-03 05:58:53.000	2011-06-02 21:58:53.000	2011-06-02 21:58:53.000
6162HOUS	2011-06-03 06:00:00.000	1	0	24	41	0	2011-06-03 05:58:52.000	2011-06-02 21:58:52.000	2011-06-02 21:58:52.000
61629800	2011-06-03 06:00:00.000	1	0	24	41	0	2011-06-03 05:58:53.000	2011-06-02 21:58:53.000	2011-06-02 21:58:53.000
61607000	2011-06-03 06:00:00.000	1	0.5	24	41	0	2011-06-03 05:58:13.000	2011-06-02 21:58:13.000	2011-06-02 21:58:13.000
61628200	2011-06-03 06:00:00.000	1	0	24	41	0	2011-06-03 05:58:53.000	2011-06-02 21:58:53.000	2011-06-02 21:58:53.000

[patrickjiang]

Mark！

[-晴天]

天哪......

;with c1 as(
select stcd,ymdhm,deval,valtyp from st_rtsr_r
union all
select stcd,ymdhm,0 as deval,(case when valtyp=11 then 24 else 1 end)valtyp from(
select stcd,ymdhm,deval,valtyp from st_rtsr_r a where not exists(select 1 from st_rtsr_r where stcd=a.stcd and ymdhm=a.ymdhm and valtyp<>a.valtyp)
)t
),c2 as(
select a.stcd,a.ymdhm,a.deval v1,b.deval v2
from c1 a inner join c1 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm and a.valtyp=b.valtyp-13
)
,c3 as(
select a.stcd,a.ymdhm,(case when convert(varchar(2),a.ymdhm,8)='08' then (select top 1 deval from st_rtsr_r where stcd=a.stcd and ymdhm=dateadd(hh,1,a.ymdhm) and valtyp=24)else a.deval-b.deval end)v3 from st_rtsr_r a inner join st_rtsr_r b on a.stcd=b.stcd and datediff(hh,b.ymdhm,a.ymdhm)=1-- and a.valtyp=24 and b.valtyp=24
where a.valtyp=24 and b.valtyp=24 --order by a.stcd,a.ymdhm
)
select a.stcd,a.ymdhm,a.v1,a.v2,isnull(b.v3,0)v3 from c2 a left join c3 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm order by a.stcd,a.ymdhm
go
--drop table st_rtsr_r
/*
stcd     ymdhm                   v1                     v2                     v3
-------- ----------------------- ---------------------- ---------------------- ----------------------
06600    2011-06-03 06:00:00.000 50.07                  0                      0
06600    2011-06-03 07:00:00.000 50.07                  0                      0
06600    2011-06-03 08:00:00.000 50.07                  0                      0
06600    2011-06-03 09:00:00.000 50.07                  0                      0
07000    2011-06-03 06:00:00.000 17.02                  0.5                    0
07000    2011-06-03 07:00:00.000 17.02                  0.5                    0
07000    2011-06-03 08:00:00.000 17.02                  0.5                    0
07210    2011-06-03 06:00:00.000 16.89                  0.5                    0
07210    2011-06-03 07:00:00.000 16.9                   0.5                    0
07210    2011-06-03 08:00:00.000 16.88                  0.5                    0
07210    2011-06-03 09:00:00.000 16.89                  0                      -0.5
07220    2011-06-03 06:00:00.000 11.97                  0                      0
07220    2011-06-03 07:00:00.000 11.97                  0                      0
07220    2011-06-03 08:00:00.000 11.96                  0                      0
07220    2011-06-03 09:00:00.000 11.97                  0                      0

(15 行受影响)


*/

[风影枫林]

老大,帮忙看下吧.

[-晴天]

[Quote=引用 22 楼 huang005x 的回复:]
老大,帮忙看下吧.
[/Quote]
对于客户端连接来说是没问题的,因为每个会话结束后,临时表会自动删除.如果一定要删除,那你在这些语句后加一个
drop table #1,#2,#3

[-晴天]

运行时间问题,你的数据是不是远远超过你给的上面这些?
如果仅上面的,我这儿0秒.

[风影枫林]

在SQL语句没改之前, 我执行很快啊, 改了之后, 就要花2分钟来执行了

而且每次执行完之后都要删除#1,#2,#3

[风影枫林]

好象是创建临时表花了不少时间

[风影枫林]

想到办法了, 临时表#1,#2,#3可以只插入最近2个小时的数据,这样程序应该快多了.

麻烦把SQL再帮忙改下吧...

[-晴天]

[Quote=引用 27 楼 huang005x 的回复:]
想到办法了, 临时表#1,#2,#3可以只插入最近2个小时的数据,这样程序应该快多了.

麻烦把SQL再帮忙改下吧...
[/Quote]
这个恐怕不行吧...
除非你的数据就是两小时的.
按道理临时表不会对速度影响很大的.

[风影枫林]

我每次只查询最近2小时的数据.

往临时表里存入数据应该要花很长时间的吧, 执行上面的SQL语句,当SQL正在执行的时候我点取消,就会显示多少行被影响,说明这段时间程序一直都在往临时表里插入数据

[风影枫林]

这样是不是每次都新建了3张表啊?
我执行这条语句花了2分钟,
而且执行完之后还必须把#1,#2,#3删除, 要不下次执行的时候就会报对象已存在的错

[风影枫林]

这个没救了吗?