34,576
社区成员
发帖
与我相关
我的任务
分享
select Rdt,sHour=case
when (a.Hours-(select max(Hours) from AirRecord where Kid=a.Kid and Rdt<a.Rdt))>24
then 24+(select max(Hours) from AirRecord where Kid=a.Kid and Rdt<a.Rdt)
else a.Hours end,a.Kid
from AirRecord as a
where Kid=110
and Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
select a.Rdt,
(case when a.Hours-b.Hours>24 then b.Hours+24 else a.Hours end) as Hours, a.Kid
from # a left join # b on a.Kid=b.Kid and a.Rdt=b.Rdt+1
where a.Kid=110 and a.Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
--这个是点对点的比较
select Rdt,sHour=case
when (a.Hours-(select Hours from # where Kid=a.Kid and Rdt+1=a.Rdt))>24
then 24+(select Hours from # where Kid=a.Kid and Rdt+1=a.Rdt)
else a.Hours end,a.Kid
from # as a
where Kid=110
and Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
-- 这个是今天7点跟昨天最大的Hours值比较
select Rdt,sHour=case
when (a.Hours-(select max(Hours) from # where Kid=a.Kid and datediff(day,Rdt,a.Rdt)=1))>24
then 24+(select max(Hours) from # where Kid=a.Kid and datediff(day,Rdt,a.Rdt)=1)
else a.Hours end,a.Kid
from # as a
where Kid=110
and Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
时间 Hours Kid 差值
2010-8-31 7:00 1095.250244 110
2010-9-1 7:00 1119.533569 110 24.283325
2010-9-2 7:00 1143.300293 110 23.766724
2010-9-3 7:00 1167.483643 110 24.18335
2010-9-4 7:00 1191.250366 110 23.766723
2010-9-5 7:00 1215.533691 110 24.283325
2010-9-6 7:00 1239.283691 110 23.75
2010-9-7 7:00 1263.467041 110 24.18335
2010-9-8 7:00 1287.233643 110 23.766602
2010-9-9 7:00 1311.516968 110 24.283325
2010-9-10 7:00 1335.283691 110 23.766723
2010-9-11 7:00 1359.467041 110 24.18335
2010-9-12 7:00 1383.233643 110 23.766602
2010-9-13 7:00 1407.516968 110 24.283325
2010-9-14 7:00 1431.266968 110 23.75
2010-9-15 7:00 1455.450317 110 24.183349
2010-9-16 7:00 1479.217041 110 23.766724
2010-9-17 7:00 1503.500366 110 24.283325
2010-9-18 7:00 1527.250366 110 23.75
2010-9-19 7:00 1551.450317 110 24.199951
2010-9-20 7:00 1575.217041 110 23.766724
2010-9-21 7:00 1599.500366 110 24.283325
2010-9-22 7:00 1623.233765 110 23.733399
2010-9-23 7:00 1647.450317 110 24.216552
2010-9-24 7:00 1671.217041 110 23.766724
2010-9-25 7:00 1695.500366 110 24.283325
2010-9-26 7:00 1719.233765 110 23.733399
2010-9-27 7:00 1743.450439 110 24.216674
2010-9-28 7:00 1767.217041 110 23.766602
2010-9-29 7:00 1791.500366 110 24.283325
2010-9-30 7:00 1815.217041 110 23.716675
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(Rdt datetime, Hours float, Kid int)
insert into #
select '2010-08-31 07:00:00.000', 1095.2502440000001, 110 union all
select '2010-09-01 07:00:00.000', 1119.5335689999999, 110 union all
select '2010-09-02 07:00:00.000', 1143.300293, 110 union all
select '2010-09-03 07:00:00.000', 1167.483643, 110 union all
select '2010-09-04 07:00:00.000', 1191.250366, 110 union all
select '2010-09-05 07:00:00.000', 1215.5336910000001, 110 union all
select '2010-09-06 07:00:00.000', 1239.2836910000001, 110 union all
select '2010-09-07 07:00:00.000', 1263.4670410000001, 110 union all
select '2010-09-08 07:00:00.000', 1287.233643, 110 union all
select '2010-09-09 07:00:00.000', 1311.5169679999999, 110 union all
select '2010-09-10 07:00:00.000', 1335.2836910000001, 110 union all
select '2010-09-11 07:00:00.000', 1359.4670410000001, 110 union all
select '2010-09-12 07:00:00.000', 1383.233643, 110 union all
select '2010-09-13 07:00:00.000', 1407.5169679999999, 110 union all
select '2010-09-14 07:00:00.000', 1431.2669679999999, 110 union all
select '2010-09-15 07:00:00.000', 1455.450317, 110 union all
select '2010-09-16 07:00:00.000', 1479.2170410000001, 110 union all
select '2010-09-17 07:00:00.000', 1503.500366, 110 union all
select '2010-09-18 07:00:00.000', 1527.250366, 110 union all
select '2010-09-19 07:00:00.000', 1551.450317, 110 union all
select '2010-09-20 07:00:00.000', 1575.2170410000001, 110 union all
select '2010-09-21 07:00:00.000', 1599.500366, 110 union all
select '2010-09-22 07:00:00.000', 1623.2337649999999, 110 union all
select '2010-09-23 07:00:00.000', 1647.450317, 110 union all
select '2010-09-24 07:00:00.000', 1671.2170410000001, 110 union all
select '2010-09-25 07:00:00.000', 1695.500366, 110 union all
select '2010-09-26 07:00:00.000', 1719.2337649999999, 110 union all
select '2010-09-27 07:00:00.000', 1743.450439, 110 union all
select '2010-09-28 07:00:00.000', 1767.2170410000001, 110 union all
select '2010-09-29 07:00:00.000', 1791.500366, 110 union all
select '2010-09-30 07:00:00.000', 1815.2170410000001, 110
;with cte as
(
select id=row_number()over(order by Rdt),* from #
)
select a.*,
newhours = case when a.Hours-b.Hours>24 then b.Hours+24 else a.Hours end
from cte a left join cte b on a.id=b.id+1
/*
id Rdt Hours Kid newhours
-------------------- ----------------------- ---------------------- ----------- ----------------------
1 2010-08-31 07:00:00.000 1095.250244 110 1095.250244
2 2010-09-01 07:00:00.000 1119.533569 110 1119.250244
3 2010-09-02 07:00:00.000 1143.300293 110 1143.300293
4 2010-09-03 07:00:00.000 1167.483643 110 1167.300293
5 2010-09-04 07:00:00.000 1191.250366 110 1191.250366
6 2010-09-05 07:00:00.000 1215.533691 110 1215.250366
7 2010-09-06 07:00:00.000 1239.283691 110 1239.283691
8 2010-09-07 07:00:00.000 1263.467041 110 1263.283691
9 2010-09-08 07:00:00.000 1287.233643 110 1287.233643
10 2010-09-09 07:00:00.000 1311.516968 110 1311.233643
11 2010-09-10 07:00:00.000 1335.283691 110 1335.283691
12 2010-09-11 07:00:00.000 1359.467041 110 1359.283691
13 2010-09-12 07:00:00.000 1383.233643 110 1383.233643
14 2010-09-13 07:00:00.000 1407.516968 110 1407.233643
15 2010-09-14 07:00:00.000 1431.266968 110 1431.266968
16 2010-09-15 07:00:00.000 1455.450317 110 1455.266968
17 2010-09-16 07:00:00.000 1479.217041 110 1479.217041
18 2010-09-17 07:00:00.000 1503.500366 110 1503.217041
19 2010-09-18 07:00:00.000 1527.250366 110 1527.250366
20 2010-09-19 07:00:00.000 1551.450317 110 1551.250366
21 2010-09-20 07:00:00.000 1575.217041 110 1575.217041
22 2010-09-21 07:00:00.000 1599.500366 110 1599.217041
23 2010-09-22 07:00:00.000 1623.233765 110 1623.233765
24 2010-09-23 07:00:00.000 1647.450317 110 1647.233765
25 2010-09-24 07:00:00.000 1671.217041 110 1671.217041
26 2010-09-25 07:00:00.000 1695.500366 110 1695.217041
27 2010-09-26 07:00:00.000 1719.233765 110 1719.233765
28 2010-09-27 07:00:00.000 1743.450439 110 1743.233765
29 2010-09-28 07:00:00.000 1767.217041 110 1767.217041
30 2010-09-29 07:00:00.000 1791.500366 110 1791.217041
31 2010-09-30 07:00:00.000 1815.217041 110 1815.217041
*/
2010-08-31 07:00:00.000 1095.2502440000001 110
2010-09-01 07:00:00.000 1119.5335689999999 110
2010-09-02 07:00:00.000 1143.300293 110
2010-09-03 07:00:00.000 1167.483643 110
2010-09-04 07:00:00.000 1191.250366 110
2010-09-05 07:00:00.000 1215.5336910000001 110
2010-09-06 07:00:00.000 1239.2836910000001 110
2010-09-07 07:00:00.000 1263.4670410000001 110
2010-09-08 07:00:00.000 1287.233643 110
2010-09-09 07:00:00.000 1311.5169679999999 110
2010-09-10 07:00:00.000 1335.2836910000001 110
2010-09-11 07:00:00.000 1359.4670410000001 110
2010-09-12 07:00:00.000 1383.233643 110
2010-09-13 07:00:00.000 1407.5169679999999 110
2010-09-14 07:00:00.000 1431.2669679999999 110
2010-09-15 07:00:00.000 1455.450317 110
2010-09-16 07:00:00.000 1479.2170410000001 110
2010-09-17 07:00:00.000 1503.500366 110
2010-09-18 07:00:00.000 1527.250366 110
2010-09-19 07:00:00.000 1551.450317 110
2010-09-20 07:00:00.000 1575.2170410000001 110
2010-09-21 07:00:00.000 1599.500366 110
2010-09-22 07:00:00.000 1623.2337649999999 110
2010-09-23 07:00:00.000 1647.450317 110
2010-09-24 07:00:00.000 1671.2170410000001 110
2010-09-25 07:00:00.000 1695.500366 110
2010-09-26 07:00:00.000 1719.2337649999999 110
2010-09-27 07:00:00.000 1743.450439 110
2010-09-28 07:00:00.000 1767.2170410000001 110
2010-09-29 07:00:00.000 1791.500366 110
2010-09-30 07:00:00.000 1815.2170410000001 110
--isnull ,使用别名
select a.Rdt,sHour=case
when (a.Hours-isnull((select max(Hours) from AirRecord b where b.Kid=a.Kid and Rdt<a.Rdt),0))>24
then 24+isnull((select max(Hours) from AirRecord c where c.Kid=a.Kid and Rdt<a.Rdt),0)
else a.Hours end,a.Kid
from AirRecord as a
where Kid=110
and a.Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
select Rdt,sHour=(
case
when (a.Hours-(select max(Hours) from AirRecord where Kid=a.Kid and Rdt<a.Rdt))>24
then 24+(select max(Hours) from AirRecord where Kid=a.Kid and Rdt<a.Rdt)
else a.Hours end),
a.Kid
from AirRecord as a
where Kid=110
and Rdt>'2010-8-31' and a.Rdt<'2010-10-1'
and datepart(hh,a.Rdt)=7
最好给出完整的表结构,测试数据,计算方法和正确结果.