？？？？？高分求助数据库查询问题？？？

[wangyue4]

数据库表大致是这样
字段datetime 存放时间
字段fcsthour，内容为0，12，24，36，48这五个数之一。代表时次，
字段value内容为数据值
当fcsthour=0时value代表真实值，fcsthour=24时value代表其24小时之后的预报值，此时需要将datetime +24小时，得到未来的时间，再select此时间并且where fcsthour = 0.这时就求出未来24小时的真实值。

我要做的是对这个表做误差统计。
我目前的思路很笨很慢
我的思路是
1、先select出所有fcsthour!=0的预报值并存入datatable中
2、datatable做循环，每次再select出真实值，然后做误差统计
这样速度特别慢，请问有没有再好一点的思路。这个可不可以一条sql语句就搞定？
不知道我说的明不明白，谢谢大家关注一下

[Olive_Guo]

没太听明白，关注中……

[zhang523012313]

LZ你把你做的写出来给我看看，就明白啦
~~你这个写的让人看看的飘飘然

[yjl49]

select *
from table_aaa t
where t.datetime in
(
select case when fcsthour =0 then datetime when fcsthour=12 then datetime +1/24 when fcsthour = 24 then datetime +1 when fcsthour = 36 then datetime +3/24 when fcsthour = 48 then datetime +2
from table_aaa
)
order by datetime

查出各个时段真实值

[yjl49]

[Quote=引用 3 楼 yjl49 的回复:]
select *
from table_aaa t
where t.datetime in
(
select case when fcsthour =0 then datetime when fcsthour=12 then datetime +1/24 when fcsthour = 24 then datetime +1 when fcsthour = 36 then dateti……
[/Quote]


select *
from table_aaa t
where t.datetime in
(
select case when fcsthour =0 then datetime when fcsthour=12 then datetime +1/24 when fcsthour = 24 then datetime +1 when fcsthour = 36 then datetime +3/24 when fcsthour = 48 then datetime +2 end 'datetime'
from table_aaa
)
order by datetime



----没写完

[k0mmDu]

假设fcsthour是12小时,表叫tb1
select t1.value,t2.value from tb1 as t1 inner join tb1 as t2 on t1.datetime+t1.fcsthour=t2.datetime where t1.fcsthour=12 and t2.fcsthour=0
不知道可不可以

[chinesesword]

select * from
(select value tb.datetime xx from tb where td.fcsthour = 0) t2
left outer join
(select sum(value),count(1) , tb.datetime + td.fcsthour xx
from tb group by tb.datetime + td.fcsthour) t1
on t2.xx = t2.xx

[k0mmDu]

select t1.value,t2.value 
from tb1 as t1 inner join tb1 as t2 
on dateadd(hh,t1.fcsthour,t1.datetime)=t2.datetime 
where t1.fcsthour=12 and t2.fcsthour=0

调试发现不对 稍微修改下

[chinesesword]

select * from td
go
SELECT *
FROM
(select value, dt xx from td where fc = 0 ) t1
left outer join
(select sum(value) v1 ,count(1) v2 , (dt + fc) xx from td group by (dt + fc)) t2
on t1.xx = t2.xx
===========================
1 0 20
1 1 23
1 2 33
1 3 50
2 0 20
2 1 40
2 2 30
2 3 40
3 0 30
3 1 10
3 2 20
3 3 30
4 0 10
4 1 30
4 2 50
4 3 10
==========================================
20 1 20 1 1
20 2 43 2 2
30 3 103 3 3
10 4 100 4 4
================
貌似再改改就可以满足楼主的需求了

[k0mmDu]

接上
表结构

USE [test]
GO
/****** 对象:  Table [dbo].[tb1]    脚本日期: 10/18/2010 17:28:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb1](
	[datetime] [datetime] NOT NULL,
	[fcsthour] [int] NOT NULL,
	[value] [int] NOT NULL
) ON [PRIMARY]

数据

INSERT INTO [test].[dbo].[tb1]
           ([datetime]
           ,[fcsthour]
           ,[value])
     VALUES
           ('2010-10-18 0:00:00'
           ,48
           ,40)
INSERT INTO [test].[dbo].[tb1]
           ([datetime]
           ,[fcsthour]
           ,[value])
     VALUES
           ('2010-10-18 12:00:00'
           ,36
           ,30)
INSERT INTO [test].[dbo].[tb1]
           ([datetime]
           ,[fcsthour]
           ,[value])
     VALUES
           ('2010-10-20 00:00:00'
           ,0
           ,0)

select t1.fcsthour as 时间,t1.[value] as 预测,t2.[value] as 实际,t1.[value]-t2.[value] as 误差
from tb1 as t1 left outer join tb1 as t2 
on dateadd(hh,t1.fcsthour,t1.datetime)=t2.datetime 
where t2.fcsthour=0

测试结果
时间 预测 实际 误差
48 40 0 40
36 30 0 30
0 0 0 0
大概就是这样了

[SQL77]

给表结构数据和结果，呵呵，你这样描述没看明白