使用SQL进行计算

Question

现在要将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

Answer 1

之前那个我怎么运行都报错
 无效列名  name 
无效列名 time

Answer 2

请问:
--假设现在时间为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
同一个时间的value相减,得0,但这和11:05:00又有什么关系?--假设现在时间为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
同上问.你高估了我的汉语理解能力.

Answer 3

不明白.
我就是用的2008,但我这儿怎么不报错呢?
create table table1(name varchar(10),time datetime,value int,type int)
insert into table1 select 'A','2011-6-13 11:00:00',51,1
insert into table1 select 'B','2011-6-13 12:00:00',35,1
insert into table1 select 'C','2011-6-13 11:00:00',43,1
insert into table1 select 'B','2011-6-13 12:00:00',8, 2
insert into table1 select 'A','2011-6-13 11:00:00',4, 2
insert into table1 select 'C','2011-6-13 12:00:00',10,2
insert into table1 select 'C','2011-6-13 11:00:00',7, 2
insert into table1 select 'A','2011-6-13 12:00:00',6, 2
insert into table1 select 'A','2011-6-13 12:00:00',54,1
insert into table1 select 'B','2011-6-13 11:00:00',3, 2
insert into table1 select 'B','2011-6-13 11:00:00',38,1
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,a.v1,a.v2,a.v2-b.v2 v3 
from c2 a left join(
select * from c2 a where not exists(select 1 from c2 where name=a.name and time<a.time)
)b on a.name=b.name order by name,time
/*
name       time                    v1          v2          v3
---------- ----------------------- ----------- ----------- -----------
A          2011-06-13 11:00:00.000 51          4           0
A          2011-06-13 12:00:00.000 54          6           2
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(6 行受影响)
*/

Answer 4

你现在的这个数据,我用原来的语句,结果是:
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,a.v1,a.v2,a.v2-b.v2 v3 
from c2 a left join(
select * from c2 a where not exists(select 1 from c2 where name=a.name and time<a.time)
)b on a.name=b.name order by name,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           1
A          2011-06-13 09:00:00.000 0           3           0
A          2011-06-13 10:00:00.000 0           6           3
A          2011-06-13 11:00:00.000 51          8           5
A          2011-06-13 12:00:00.000 54          9           6
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 行受影响)
*/
不清楚是否符合你的要求.

Answer 5

进行查询的时间并不是整点,我没是在11点过5分的时候进行查询,将数据库中11点整的数据减去10点整的数据得到value3的值

Answer 6

进行查询的时间并不是整点,我们是在11点过5分的时候进行查询,将数据库中11点整的数据减去10点整的数据得到对应11点整的value3的值同上, 当我们在12点过5分进行查询的时候,就需要将12点整的数据减去11点整的数据,得到对应12点整的value3的值

Answer 7

--我想要的执行结果是
--注意:特殊情况,8点整的value3值是特殊情况
--如果当前时间为早上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
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

Answer 8

是这样么?
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 行受影响)*/

Answer 9

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

Answer 10

我把上面的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

Answer 11

--另外,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

Answer 12

天哪......
;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 行受影响)
*/

Answer 13

[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

Answer 14

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

Answer 15

这样,就可以在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.ymdhmselect 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

Answer 16

对于客户端连接来说是没问题的,因为每个会话结束后,临时表会自动删除.如果一定要删除,那你在这些语句后加一个
drop table #1,#2,#3

Answer 17

在SQL语句没改之前, 我执行很快啊, 改了之后,  就要花2分钟来执行了而且每次执行完之后都要删除#1,#2,#3

Answer 18

想到办法了,  临时表#1,#2,#3可以只插入最近2个小时的数据,这样程序应该快多了.麻烦把SQL再帮忙改下吧...

Answer 19

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