请大侠们帮我看看下面这个语句有什么问题？

2010-10-20 08:57:29 175 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

关键词‖旋变传感器旋转变压器(以下简称旋变)安装在永磁同步驱动电机的非传动端，其作用是检测转子的位置和速度。目前，通常采采用结构简单、坚固耐用、环境适应性好的磁阻式旋变作为位置传感器。阅读详情

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

...全文

字符串替换—牛客网（C++实现）一.题目描述请你实现一个简单的字符串替换函数。原串中需要替换的占位符为"%s",请按照参数列表的顺序一一替换占位符。若参数列表的字符数大于占位符个数。则将剩下的参数字符添加到字符串的结尾。给定一个字符串A，同时给定它的长度n及参数字符数组arg，请返回替换后的字符串。保证参数个数大于等于占位符个数。保证原串由大小写英文字母组成，同时长度小于等于500。测试样例： “A%sC%sE”,7,[‘... 阅读详情

youzhj

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

youzhj 2010-10-20

打赏
举报

[Quote=引用 20 楼 sqlcenter 的回复:]
这样left join不用子查询可能效率高点

SQL code
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……
[/Quote]太谢谢你了，这个我也测试测试去。

SQLCenter 2010-10-20

打赏
举报

这样left join不用子查询可能效率高点

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

youzhj 2010-10-20

打赏
举报

[Quote=引用 17 楼 sqlcenter 的回复:]
SQL code
--这个是点对点的比较
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,……
[/Quote]看来是我没弄清楚点对点的概念，真是对不起。你给的点对点的语句是我想要的，谢谢你，马上结贴，这是高手啊。

youzhj 2010-10-20

打赏
举报

SQLCenter 2010-10-20

打赏
举报

--这个是点对点的比较

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

youzhj 2010-10-20

打赏
举报

[Quote=引用 15 楼 sqlcenter 的回复:]
确认一件事，你是今天的7点要跟昨天的7点相比较，还是？
[/Quote]是的，今天减去昨天的7点值之差不大于24。

SQLCenter 2010-10-20

打赏
举报

确认一件事，你是今天的7点要跟昨天的7点相比较，还是？

SQLCenter 2010-10-20

打赏
举报

哦，你的语句是逻辑有问题。
== 我写一个

youzhj 2010-10-20

打赏
举报

[Quote=引用 12 楼 sqlcenter 的回复:]
没错的啊，我用你的语句跟我语句比较，双向 except 都是空集，证明产生的结果集完全相同。
[/Quote]老大，您在看看您6楼那个结果，Hours字段和newHours字段是不一样的啊，我就是想要newHours字段的结果。

SQLCenter 2010-10-20

打赏
举报

没错的啊，我用你的语句跟我语句比较，双向 except 都是空集，证明产生的结果集完全相同。

youzhj 2010-10-20

打赏
举报

[Quote=引用 10 楼 sqlcenter 的回复:]
2000你原来那条语句有什么问题？
效率？
[/Quote]原来那个得不到我要的结果，我要结果其实就是6楼的结果，但是你那是2005的语法，我这2000用不了。

SQLCenter 2010-10-20

打赏
举报

2000你原来那条语句有什么问题？
效率？

youzhj 2010-10-20

打赏
举报

我用的是SQL2000版本，帮帮忙啊，谢谢了。

youzhj 2010-10-20

打赏
举报

[Quote=引用 6 楼 sqlcenter 的回复:]
SQL code
--> 测试数据：#
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, 11……
[/Quote]能给个sql2000的句子么？

youzhj 2010-10-20

打赏
举报



时间   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

我想让上面那些差值都小于等于24，该怎么处理呢？

SQLCenter 2010-10-20

打赏
举报

--> 测试数据：#

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

*/

youzhj 2010-10-20

打赏
举报

表AirRecord(Rdt,Hours,Kid)，其中Hours字段是float型的，我想查询每天7点的数据，并且须保证所有相邻两天的Hours值之差不大于24。如果差值大于24的话，就让后一日的Hours=前一日Hours+24。
原始数据：

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

abuying 2010-10-20

打赏
举报

--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

youzhj 2010-10-20

打赏
举报

[Quote=引用 2 楼 dawugui 的回复:]
SQL code
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 ……
[/Quote]谢谢老大提醒啊，我其实知道发帖规矩，只是对这个问题迫不及待，所以先发上来了，由于数据有点多，现在正在整理，等一下就发上数据和想要的结果来。

dawugui 2010-10-20

打赏
举报

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

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

加载更多回复（1）

请大侠们帮我看看下面这个语句有什么问题？

相关推荐