select 结果=(case when b.结果 isnull or a.结果 is null then 999 else a.结果-b.结果),isnull(a.名称,b.名称),isnull(a.时间,b.时间) from tablename a full join tablename b on a.名称=b.名称 and cast(a.时间 as int) -2)=cast(b.时间 as int)
改: select 结果=(case when b.结果 is null or a.结果 is null then 999 else a.结果-b.结果 end ) ,isnull(a.名称,b.名称), a.时间 from tablename a left join tablename b on a.名称=b.名称 and cast(a.时间 as int) =cast(b.时间 as int)+2 order by isnull(a.时间,b.时间) desc,isnull(a.名称,b.名称)
测试结果如下(自己感觉不完整,但想不出怎么解决,再顶一下): 结果 时间 ------------- ---- ---------- -.2 a 2004051208 .9 b 2004051208 .1 c 2004051208 3.1 d 2004051208 3.7 e 2004051208 .8 a 2004051207 .9 b 2004051207 999.0 c 2004051207 .2 d 2004051207 -.4 e 2004051207 .0 a 2004051206 .0 b 2004051206 999.0 c 2004051206 .0 d 2004051206 .0 e 2004051206 999.0 a 2004051205 999.0 b 2004051205 999.0 d 2004051205 999.0 e 2004051205 999.0 a 2004051204 999.0 b 2004051204 999.0 d 2004051204 999.0 e 2004051204(所影响的行数为 23 行)
select (case when b.名称 is null then 999 else (a.结果-b.结果) end) as 结果, a.名称,a.时间 from test a left outer join test b on a.名称=b.名称 and b.时间=dateadd(hour,-2,a.时间)
--建立测试环境 create table test ( 结果 decimal(15,5), 名称 varchar(10), 时间 datetime )insert into test values(1.1 ,'a','20040512 08:00:00') insert into test values(2.3 ,'b','20040512 08:00:00') insert into test values(1.2 ,'c','20040512 08:00:00') insert into test values(4.3 ,'d','20040512 08:00:00') insert into test values(5.4 ,'e','20040512 08:00:00')insert into test values(2.1 ,'a','20040512 07:00:00') insert into test values(2.3 ,'b','20040512 07:00:00') insert into test values(3.1 ,'c','20040512 07:00:00') insert into test values(1.4 ,'d','20040512 07:00:00') insert into test values(1.3 ,'e','20040512 07:00:00')insert into test values(1.3 ,'a','20040512 06:00:00') insert into test values(1.4 ,'b','20040512 06:00:00') insert into test values(1.1 ,'c','20040512 06:00:00') insert into test values(1.2 ,'d','20040512 06:00:00') insert into test values(1.7 ,'e','20040512 06:00:00')
insert into test values(1.3 ,'a','20040512 05:00:00') insert into test values(1.4 ,'b','20040512 05:00:00') insert into test values(1.2 ,'d','20040512 05:00:00') insert into test values(1.7 ,'e','20040512 05:00:00')insert into test values(1.3 ,'a','20040512 04:00:00') insert into test values(1.4 ,'b','20040512 04:00:00') insert into test values(1.2 ,'d','20040512 04:00:00') insert into test values(1.7 ,'e','20040512 04:00:00')--执行查询select (case when b.名称 is null then 999 else (a.结果-b.结果) end) as 结果, a.名称,a.时间 from test a left outer join test b on a.名称=b.名称 and b.时间=dateadd(hour,-2,a.时间)结果 名称 时间 ------------------ ---------- ------------------------------------------------------ -.20000 a 2004-05-12 08:00:00.000 .90000 b 2004-05-12 08:00:00.000 .10000 c 2004-05-12 08:00:00.000 3.10000 d 2004-05-12 08:00:00.000 3.70000 e 2004-05-12 08:00:00.000 .80000 a 2004-05-12 07:00:00.000 .90000 b 2004-05-12 07:00:00.000 999.00000 c 2004-05-12 07:00:00.000 .20000 d 2004-05-12 07:00:00.000 -.40000 e 2004-05-12 07:00:00.000 .00000 a 2004-05-12 06:00:00.000 .00000 b 2004-05-12 06:00:00.000 999.00000 c 2004-05-12 06:00:00.000 .00000 d 2004-05-12 06:00:00.000 .00000 e 2004-05-12 06:00:00.000 999.00000 a 2004-05-12 05:00:00.000 999.00000 b 2004-05-12 05:00:00.000 999.00000 d 2004-05-12 05:00:00.000 999.00000 e 2004-05-12 05:00:00.000 999.00000 a 2004-05-12 04:00:00.000 999.00000 b 2004-05-12 04:00:00.000 999.00000 d 2004-05-12 04:00:00.000 999.00000 e 2004-05-12 04:00:00.000(23 row(s) affected)
select 结果=(case when b.结果 is null or a.结果 is null then 999 else a.结果-b.结果 end ) ,isnull(a.名称,b.名称), a.时间 from table1 a left join table1 b on a.名称=b.名称 and cast(a.时间 as int) =cast(b.时间 as int)+2 order by isnull(a.时间,b.时间) desc,isnull(a.名称,b.名称)我的视图名为 字段:结果,名称,时间全为char table1, 在查询分析器中提示: 数据类型而言运算符无效。运算符为 subtract,类型为 char。
应该用datediff ,同意 eru()
to eru(): 因为字段:结果,名称,时间全为char类型, 所以不好处理,如果时间字段时间类型的,用你的方法可能可以。 如何处理全为char类型的字段?
结果为: select (case when b.名称 is null then 999 else (a.结果-b.结果) end) as 结果, a.名称,a.时间 from table1 a left outer join table1 b on a.名称=b.名称 and CAST(SUBSTRING(b.时间, 1, 4)+ '-' + SUBSTRING(b.时间, 5, 2) + '-' + SUBSTRING(b.时间, 7, 2) + ' ' + SUBSTRING(b.时间, 9, 2) + ':00:00' AS datetime)=dateadd(hour,-2,CAST(SUBSTRING(a.时间, 1, 4)+ '-' + SUBSTRING(a.时间, 5, 2) + '-' + SUBSTRING(a.时间, 7, 2) + ' ' + SUBSTRING(a.时间, 9, 2) + ':00:00' AS datetime))可能可以,测试过后再来.
--全部是字符型就这样写查询:select 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end,a.名称,a.时间 from table1 a left join table1 b on a.名称=b.名称 and datediff(hour,stuff(b.时间,9,0,' ')+':00',stuff(a.时间,9,0,' ')+':00')=2
--测试--测试数据 create table 表(结果 char(20),名称 char(10),时间 char(10)) insert 表 select '1.1','a','2004051208' union all select '2.3','b','2004051208' union all select '1.2','c','2004051208' union all select '4.3','d','2004051208' union all select '5.4','e','2004051208' union all select '2.1','a','2004051207' union all select '2.3','b','2004051207' union all select '3.1','c','2004051207' union all select '1.4','d','2004051207' union all select '1.3','e','2004051207' union all select '1.3','a','2004051206' union all select '1.4','b','2004051206' union all select '1.1','c','2004051206' union all select '1.2','d','2004051206' union all select '1.7','e','2004051206' union all select '1.3','a','2004051205' union all select '1.4','b','2004051205' union all select '1.2','d','2004051205' union all select '1.7','e','2004051205' union all select '1.3','a','2004051204' union all select '1.4','b','2004051204' union all select '1.2','d','2004051204' union all select '1.7','e','2004051204' go--查询处理 select 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end ,a.名称,a.时间 from 表 a left join 表 b on a.名称=b.名称 and datediff(hour,stuff(b.时间,9,0,' ')+':00' ,stuff(a.时间,9,0,' ')+':00')=2 go--删除测试 drop table 表/*--测试结果结果 名称 时间 ------------- ---------- ---------- -.2 a 2004051208 .9 b 2004051208 .1 c 2004051208 3.1 d 2004051208 3.7 e 2004051208 .8 a 2004051207 .9 b 2004051207 999.0 c 2004051207 .2 d 2004051207 -.4 e 2004051207 .0 a 2004051206 .0 b 2004051206 999.0 c 2004051206 .0 d 2004051206 .0 e 2004051206 999.0 a 2004051205 999.0 b 2004051205 999.0 d 2004051205 999.0 e 2004051205 999.0 a 2004051204 999.0 b 2004051204 999.0 d 2004051204 999.0 e 2004051204 --*/
谢谢各位! 结果在查询分析器中运行过了,一放在创建视图中就没法保存: 错误提示: 查询设计器不支持 CASE SQL 构造。
--你不要在查询设计器中保存,查询设计器对SQL的支持比较弱--直接在查询分析器中用SQL语句创建视图就行了.--示例 create view 视图名 as select 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end ,a.名称,a.时间 from 表 a left join 表 b on a.名称=b.名称 and datediff(hour,stuff(b.时间,9,0,' ')+':00' ,stuff(a.时间,9,0,' ')+':00')=2
create view 视图名 as select top 100 percent ---------------------------必须加上这个 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end ,a.名称,a.时间 from 表 a left join 表 b on a.名称=b.名称 and datediff(hour,stuff(b.时间,9,0,' ')+':00' ,stuff(a.时间,9,0,' ')+':00')=2 order by a.时间
运行:select * from 视图,或select count(*) from 视图 50000个数据要用15分钟多,太慢,实太忍受不了。
--不用视图用存储过程 select 结果,名称, datediff(hour,'17530101',cast(stuff(时间,9,0,' ')+':00' as datetime)) 时间 into #temp1 from 表select 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end ,a.名称,dateadd(hour,a.时间,'17530101') as 时间 from #temp1 a left join #temp1 b on a.名称=b.名称 and a.时间=b.时间+2 --是不是快很多了?
--那你试试不用order by 速度快多少--再试试这个语句 create view 视图名 as select top 100 percent ---------------------------必须加上这个 结果=case when b.名称 is null then 999 else cast(a.结果 as decimal(10,1))-b.结果 end ,a.名称,a.时间 from 表 a left join 表 b on a.名称=b.名称 and a.时间 =replace(replace(convert(varchar(13),dateadd(hour,2,stuff(b.时间,9,0,' ')+':00'),120),'-',''),' ','') order by a.时间
select 结果=(case when b.结果 is null or a.结果 is null then 999 else a.结果-b.结果 end )
,isnull(a.名称,b.名称),
a.时间 from tablename a
left join tablename b on a.名称=b.名称
and cast(a.时间 as int) =cast(b.时间 as int)+2
order by isnull(a.时间,b.时间) desc,isnull(a.名称,b.名称)
结果 时间
------------- ---- ----------
-.2 a 2004051208
.9 b 2004051208
.1 c 2004051208
3.1 d 2004051208
3.7 e 2004051208
.8 a 2004051207
.9 b 2004051207
999.0 c 2004051207
.2 d 2004051207
-.4 e 2004051207
.0 a 2004051206
.0 b 2004051206
999.0 c 2004051206
.0 d 2004051206
.0 e 2004051206
999.0 a 2004051205
999.0 b 2004051205
999.0 d 2004051205
999.0 e 2004051205
999.0 a 2004051204
999.0 b 2004051204
999.0 d 2004051204
999.0 e 2004051204(所影响的行数为 23 行)
a.名称,a.时间
from test a left outer join test b
on a.名称=b.名称 and b.时间=dateadd(hour,-2,a.时间)
create table test
(
结果 decimal(15,5),
名称 varchar(10),
时间 datetime
)insert into test values(1.1 ,'a','20040512 08:00:00')
insert into test values(2.3 ,'b','20040512 08:00:00')
insert into test values(1.2 ,'c','20040512 08:00:00')
insert into test values(4.3 ,'d','20040512 08:00:00')
insert into test values(5.4 ,'e','20040512 08:00:00')insert into test values(2.1 ,'a','20040512 07:00:00')
insert into test values(2.3 ,'b','20040512 07:00:00')
insert into test values(3.1 ,'c','20040512 07:00:00')
insert into test values(1.4 ,'d','20040512 07:00:00')
insert into test values(1.3 ,'e','20040512 07:00:00')insert into test values(1.3 ,'a','20040512 06:00:00')
insert into test values(1.4 ,'b','20040512 06:00:00')
insert into test values(1.1 ,'c','20040512 06:00:00')
insert into test values(1.2 ,'d','20040512 06:00:00')
insert into test values(1.7 ,'e','20040512 06:00:00')
insert into test values(1.3 ,'a','20040512 05:00:00')
insert into test values(1.4 ,'b','20040512 05:00:00')
insert into test values(1.2 ,'d','20040512 05:00:00')
insert into test values(1.7 ,'e','20040512 05:00:00')insert into test values(1.3 ,'a','20040512 04:00:00')
insert into test values(1.4 ,'b','20040512 04:00:00')
insert into test values(1.2 ,'d','20040512 04:00:00')
insert into test values(1.7 ,'e','20040512 04:00:00')--执行查询select (case when b.名称 is null then 999 else (a.结果-b.结果) end) as 结果,
a.名称,a.时间
from test a left outer join test b
on a.名称=b.名称 and b.时间=dateadd(hour,-2,a.时间)结果 名称 时间
------------------ ---------- ------------------------------------------------------
-.20000 a 2004-05-12 08:00:00.000
.90000 b 2004-05-12 08:00:00.000
.10000 c 2004-05-12 08:00:00.000
3.10000 d 2004-05-12 08:00:00.000
3.70000 e 2004-05-12 08:00:00.000
.80000 a 2004-05-12 07:00:00.000
.90000 b 2004-05-12 07:00:00.000
999.00000 c 2004-05-12 07:00:00.000
.20000 d 2004-05-12 07:00:00.000
-.40000 e 2004-05-12 07:00:00.000
.00000 a 2004-05-12 06:00:00.000
.00000 b 2004-05-12 06:00:00.000
999.00000 c 2004-05-12 06:00:00.000
.00000 d 2004-05-12 06:00:00.000
.00000 e 2004-05-12 06:00:00.000
999.00000 a 2004-05-12 05:00:00.000
999.00000 b 2004-05-12 05:00:00.000
999.00000 d 2004-05-12 05:00:00.000
999.00000 e 2004-05-12 05:00:00.000
999.00000 a 2004-05-12 04:00:00.000
999.00000 b 2004-05-12 04:00:00.000
999.00000 d 2004-05-12 04:00:00.000
999.00000 e 2004-05-12 04:00:00.000(23 row(s) affected)
,isnull(a.名称,b.名称),
a.时间 from table1 a
left join table1 b on a.名称=b.名称
and cast(a.时间 as int) =cast(b.时间 as int)+2
order by isnull(a.时间,b.时间) desc,isnull(a.名称,b.名称)我的视图名为
字段:结果,名称,时间全为char
table1, 在查询分析器中提示:
数据类型而言运算符无效。运算符为 subtract,类型为 char。
因为字段:结果,名称,时间全为char类型,
所以不好处理,如果时间字段时间类型的,用你的方法可能可以。
如何处理全为char类型的字段?
select (case when b.名称 is null then 999 else (a.结果-b.结果) end) as 结果,
a.名称,a.时间
from table1 a left outer join table1 b
on a.名称=b.名称 and CAST(SUBSTRING(b.时间, 1, 4)+ '-' + SUBSTRING(b.时间, 5, 2) + '-' + SUBSTRING(b.时间, 7, 2) + ' ' + SUBSTRING(b.时间, 9, 2) + ':00:00' AS datetime)=dateadd(hour,-2,CAST(SUBSTRING(a.时间, 1, 4)+ '-' + SUBSTRING(a.时间, 5, 2) + '-' + SUBSTRING(a.时间, 7, 2) + ' ' + SUBSTRING(a.时间, 9, 2) + ':00:00' AS datetime))可能可以,测试过后再来.
from table1 a left join table1 b
on a.名称=b.名称 and datediff(hour,stuff(b.时间,9,0,' ')+':00',stuff(a.时间,9,0,' ')+':00')=2
create table 表(结果 char(20),名称 char(10),时间 char(10))
insert 表 select '1.1','a','2004051208'
union all select '2.3','b','2004051208'
union all select '1.2','c','2004051208'
union all select '4.3','d','2004051208'
union all select '5.4','e','2004051208'
union all select '2.1','a','2004051207'
union all select '2.3','b','2004051207'
union all select '3.1','c','2004051207'
union all select '1.4','d','2004051207'
union all select '1.3','e','2004051207'
union all select '1.3','a','2004051206'
union all select '1.4','b','2004051206'
union all select '1.1','c','2004051206'
union all select '1.2','d','2004051206'
union all select '1.7','e','2004051206'
union all select '1.3','a','2004051205'
union all select '1.4','b','2004051205'
union all select '1.2','d','2004051205'
union all select '1.7','e','2004051205'
union all select '1.3','a','2004051204'
union all select '1.4','b','2004051204'
union all select '1.2','d','2004051204'
union all select '1.7','e','2004051204'
go--查询处理
select 结果=case when b.名称 is null then 999
else cast(a.结果 as decimal(10,1))-b.结果 end
,a.名称,a.时间
from 表 a left join 表 b
on a.名称=b.名称
and datediff(hour,stuff(b.时间,9,0,' ')+':00'
,stuff(a.时间,9,0,' ')+':00')=2
go--删除测试
drop table 表/*--测试结果结果 名称 时间
------------- ---------- ----------
-.2 a 2004051208
.9 b 2004051208
.1 c 2004051208
3.1 d 2004051208
3.7 e 2004051208
.8 a 2004051207
.9 b 2004051207
999.0 c 2004051207
.2 d 2004051207
-.4 e 2004051207
.0 a 2004051206
.0 b 2004051206
999.0 c 2004051206
.0 d 2004051206
.0 e 2004051206
999.0 a 2004051205
999.0 b 2004051205
999.0 d 2004051205
999.0 e 2004051205
999.0 a 2004051204
999.0 b 2004051204
999.0 d 2004051204
999.0 e 2004051204
--*/
结果在查询分析器中运行过了,一放在创建视图中就没法保存:
错误提示:
查询设计器不支持 CASE SQL 构造。
create view 视图名
as
select 结果=case when b.名称 is null then 999
else cast(a.结果 as decimal(10,1))-b.结果 end
,a.名称,a.时间
from 表 a left join 表 b
on a.名称=b.名称
and datediff(hour,stuff(b.时间,9,0,' ')+':00'
,stuff(a.时间,9,0,' ')+':00')=2
as
select top 100 percent ---------------------------必须加上这个
结果=case when b.名称 is null then 999
else cast(a.结果 as decimal(10,1))-b.结果 end
,a.名称,a.时间
from 表 a left join 表 b
on a.名称=b.名称
and datediff(hour,stuff(b.时间,9,0,' ')+':00'
,stuff(a.时间,9,0,' ')+':00')=2
order by a.时间
2.修改数据类型,不要全部用字符型,这样就可以节省数据转换的时间.3.设置对象的超时属性
.commandtimeout=0
.connectiontimeout=0
50000个数据要用15分钟多,太慢,实太忍受不了。
select 结果,名称, datediff(hour,'17530101',cast(stuff(时间,9,0,' ')+':00' as datetime)) 时间 into #temp1
from 表select 结果=case when b.名称 is null then 999
else cast(a.结果 as decimal(10,1))-b.结果 end
,a.名称,dateadd(hour,a.时间,'17530101') as 时间
from #temp1 a left join #temp1 b
on a.名称=b.名称
and a.时间=b.时间+2
--是不是快很多了?
create view 视图名
as
select top 100 percent ---------------------------必须加上这个
结果=case when b.名称 is null then 999
else cast(a.结果 as decimal(10,1))-b.结果 end
,a.名称,a.时间
from 表 a left join 表 b
on a.名称=b.名称 and a.时间
=replace(replace(convert(varchar(13),dateadd(hour,2,stuff(b.时间,9,0,' ')+':00'),120),'-',''),' ','')
order by a.时间
再次感谢各位的帮助。
请 zjcxc(邹建) 到新贴
http://expert.csdn.net/Expert/topic/3065/3065816.xml?temp=9.983462E-02
领分。