--注:表st_rtsr_r中数据量较大
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--将下面的SQL语句改成2000可用的
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
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
) a left join
(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
) b on a.stcd=b.stcd and a.ymdhm=b.ymdhm order by a.stcd,a.ymdhm
go
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
) a left join
(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
) b on a.stcd=b.stcd and a.ymdhm=b.ymdhm order by a.stcd,a.ymdhm
go
帮楼上格式一下,方便看
select a.stcd,a.ymdhm,a.v1,a.v2,isnull(b.v3,0)v3 from
(select a.stcd,a.ymdhm,a.deval v1,b.deval v2
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
)
a inner join c1 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm and a.valtyp=b.valtyp-13) aa
left join
(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)
b
on aa.stcd=b.stcd and aa.ymdhm=b.ymdhm order by a.astcd,aa.ymdhm
from
(
select a.stcd,a.ymdhm,a.deval v1,b.deval v2
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) a inner join (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) b on a.stcd=b.stcd and a.ymdhm=b.ymdhm and a.valtyp=b.valtyp-13) a
left join
(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
where a.valtyp=24 and b.valtyp=24) b
on a.stcd=b.stcd and a.ymdhm=b.ymdhm order by a.stcd,a.ymdhm
go
where 等关连字段可以建索引
where a.ymdhm < DATEADD(HH,-20,GETDATE())
order by a.ymdhm
go
不解决我就要在这山沟了呆一辈子了
把那台该死的机器上的该死的 sql server 2000 换成 sql server 2005 or sql server 2008!
-- 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]
--
-- GOSELECT *
INTO #TMP1
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
) C1SELECT *
INTO #TMP2
FROM (
select a.stcd,a.ymdhm,a.deval v1,b.deval v2
from #TMP1 a
inner join #TMP1 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm and a.valtyp=b.valtyp-13
) C2SELECT *
INTO #TMP3
FROM (
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
) C3select a.stcd,a.ymdhm,a.v1,a.v2,isnull(b.v3,0)v3
from #TMP2 a left join #TMP3 b on a.stcd=b.stcd and a.ymdhm=b.ymdhm
order by a.stcd,a.ymdhm
go DROP TABLE #TMP1
DROP TABLE #TMP2
DROP TABLE #TMP3