麻烦乌龟大哥帮忙看一下

2009-03-19 01:26:20 188 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

机器人抓取（一）—— ROS MoveIt! 程序控制真实UR5机器人运动（python）接上篇：ubuntu18.04 ros-melodic 安装 ur_robot_driver，驱动真实的 ur5 机器人上文在 ubuntu 系统中配置了ur 机器人ros功能包 universal_robot 和驱动 Universal_Robots_ROS_Driver，并实现了用 moveit_rviz 拖动机器人运动。本文旨在学习如何使用 moveit python 用户接口moveit_commander，实现真实的ur5 机器人轨迹规划与控制。 1. ur5 机器人控制参考：MoveIt » 阅读详情

麻烦乌龟大哥帮忙看一下，我不知道要怎么解决比较好：
我这里模拟了一个表：（实际库中的这个表的数据很大，有上百万条的数据）
DECLARE @TMP TABLE(SN INT ,EndDate DATETIME,Num DECIMAL(18,2) )
INSERT INTO @TMP
SELECT 1,CONVERT(CHAR(10),GETDATE(),120),22 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),0.1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-6,GETDATE()),120),20 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),50 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-15,GETDATE()),120),25 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-26,GETDATE()),120),48 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-37,GETDATE()),120),12 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(MONTH,-3,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-3,GETDATE()),120),22.23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),45 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-25,GETDATE()),120),22.65 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-36,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(MONTH,-7,GETDATE()),120),23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(YEAR,-1,GETDATE()),120),65

SELECT * FROM @TMP
想要得到一下的结果：
/*其中ResultDate这个字段是这样得到的：每个SN的Num值朝前取，直到数值达到100的那天的日期
SN EndDate Num ResultDate
1 2009-03-19 00:00:00.000 22.0000 2009-03-04
1 2009-03-18 00:00:00.000 1.0000 2009-02-21
1 2009-03-17 00:00:00.000 0.1000 2009-02-21
1 2009-03-13 00:00:00.000 20.0000 2009-02-21
1 2009-03-05 00:00:00.000 50.0000 2009-02-21
1 2009-03-04 00:00:00.000 25.0000 2008-12-19
1 2009-02-21 00:00:00.000 48.0000 NULL
1 2009-02-10 00:00:00.000 12.0000 NULL
1 2008-12-19 00:00:00.000 36.0000 NULL
2 2009-03-18 00:00:00.000 36.0000 2009-03-05
2 2009-03-17 00:00:00.000 12.0000 2009-02-22
2 2009-03-16 00:00:00.000 22.2300 2009-02-11
2 2009-03-05 00:00:00.000 45.0000 2008-08-19
2 2009-02-22 00:00:00.000 22.6500 2008-03-19
2 2009-02-11 00:00:00.000 12.0000 2008-03-19
2 2008-08-19 00:00:00.000 23.0000 NULL
2 2008-03-19 00:00:00.000 65.0000 NULL*/
不知道我有没有描述清楚？麻烦了！

...全文

`全网最细!! 入门必看Git教程链接:https://gitee.com/all-about-git` 全网 Git 入门教程 gitee.com。如果显示The requested URL returned error: 403错误，那就表示没有和window本地建立token令牌阅读详情

shirley_yue

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

+ 关注 + 加入社区

切换为时间正序

shirley_yue 2009-03-19

打赏
举报

我明白你们写的意思了，呵呵，谢谢 “子陌红尘” 和“乌龟”大哥啊！

dawugui 2009-03-19

打赏
举报

DECLARE @TMP TABLE(SN INT ,EndDate DATETIME,Num DECIMAL(18,2) ) 

INSERT INTO @TMP 

SELECT 1,CONVERT(CHAR(10),GETDATE(),120),22 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),1 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),0.1 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-6,GETDATE()),120),20 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),50 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-15,GETDATE()),120),25 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-26,GETDATE()),120),48 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-37,GETDATE()),120),12 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(MONTH,-3,GETDATE()),120),36 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),36 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),12 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-3,GETDATE()),120),22.23 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),45 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-25,GETDATE()),120),22.65 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-36,GETDATE()),120),12 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(MONTH,-7,GETDATE()),120),23 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(YEAR,-1,GETDATE()),120),65 



SELECT * , case when (select sum(num) from @tmp where sn = t.sn and enddate >= t.enddate) <= 100 

                then (select sum(num) from @tmp where sn = t.sn and enddate >= t.enddate)

           end sum_num 

FROM @TMP t



/*

SN          EndDate                                                Num                  sum_num                                  

----------- ------------------------------------------------------ -------------------- ---------------------------------------- 

1           2009-03-19 00:00:00.000                                22.00                22.00

1           2009-03-18 00:00:00.000                                1.00                 23.00

1           2009-03-17 00:00:00.000                                .10                  23.10

1           2009-03-13 00:00:00.000                                20.00                43.10

1           2009-03-05 00:00:00.000                                50.00                93.10

1           2009-03-04 00:00:00.000                                25.00                NULL

1           2009-02-21 00:00:00.000                                48.00                NULL

1           2009-02-10 00:00:00.000                                12.00                NULL

1           2008-12-19 00:00:00.000                                36.00                NULL

2           2009-03-18 00:00:00.000                                36.00                36.00

2           2009-03-17 00:00:00.000                                12.00                48.00

2           2009-03-16 00:00:00.000                                22.23                70.23

2           2009-03-05 00:00:00.000                                45.00                NULL

2           2009-02-22 00:00:00.000                                22.65                NULL

2           2009-02-11 00:00:00.000                                12.00                NULL

2           2008-08-19 00:00:00.000                                23.00                NULL

2           2008-03-19 00:00:00.000                                65.00                NULL



（所影响的行数为 17 行）



*/

子陌红尘 2009-03-19

打赏
举报

[Quote=引用 12 楼 shirley_yue 的回复:]
有一点我不是很明白哦，
(select sum(c.Num) from @TMP c where EndDate between (select top 1 EndDate from @TMP where SN=b.SN and EndDate>b.EndDate order by EndDate asc) and a.EndDate and SN=a.SN) <100

是什么意思？
[/Quote]

这个条件需要与之前的(select sum(Num) from @TMP where EndDate between b.EndDate and a.EndDate and SN=a.SN)>=100结合起来理解：

即起始日期到截止日期之间的Num取值求和大于等于100，但是起始日期的下一个日期(大于起始日期且最接近起始日期的那条记录)与截止日期之间Num取值求和小于100；目的是为了取得满足楼主条件的 ResultDate，并过滤Num和超出100之后的记录。

shirley_yue 2009-03-19

打赏
举报

有一点我不是很明白哦，
(select sum(c.Num) from @TMP c where EndDate between (select top 1 EndDate from @TMP where SN=b.SN and EndDate>b.EndDate order by EndDate asc) and a.EndDate and SN=a.SN)<100

是什么意思？

dawugui 2009-03-19

打赏
举报

[Quote=引用 10 楼 shirley_yue 的回复:]
哈哈，太好了，非常感谢 “子陌红尘”大哥！
[/Quote]
OK了,那我接个分.

shirley_yue 2009-03-19

打赏
举报

哈哈，太好了，非常感谢 “子陌红尘”大哥！

claro 2009-03-19

打赏
举报

帮顶

子陌红尘 2009-03-19

打赏
举报



DECLARE @TMP TABLE(SN INT ,EndDate DATETIME,Num DECIMAL(18,2) ) 

INSERT INTO @TMP 

SELECT 1,CONVERT(CHAR(10),GETDATE(),120),22 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),1 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),0.1 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-6,GETDATE()),120),20 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),50 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-15,GETDATE()),120),25 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-26,GETDATE()),120),48 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-37,GETDATE()),120),12 UNION ALL 

SELECT 1,CONVERT(CHAR(10),DATEADD(MONTH,-3,GETDATE()),120),36 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),36 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),12 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-3,GETDATE()),120),22.23 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),45 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-25,GETDATE()),120),22.65 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-36,GETDATE()),120),12 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(MONTH,-7,GETDATE()),120),23 UNION ALL 

SELECT 2,CONVERT(CHAR(10),DATEADD(YEAR,-1,GETDATE()),120),65 



select 

    a.*,b.EndDate

from 

    @TMP a

left join

    @TMP b

on

    a.SN=b.SN

    and

    a.EndDate>=b.EndDate

    and

    (select sum(Num) from @TMP where EndDate between b.EndDate and a.EndDate and SN=a.SN)>=100

    and

    (select sum(c.Num) from @TMP c where EndDate between (select top 1 EndDate from @TMP where SN=b.SN and EndDate>b.EndDate order by EndDate asc) and a.EndDate and SN=a.SN)<100



/*

SN          EndDate                                                Num                  EndDate                                                

----------- ------------------------------------------------------ -------------------- ------------------------------------------------------ 

1           2009-03-19 00:00:00.000                                22.00                2009-03-04 00:00:00.000

1           2009-03-18 00:00:00.000                                1.00                 2009-02-21 00:00:00.000

1           2009-03-17 00:00:00.000                                .10                  2009-02-21 00:00:00.000

1           2009-03-13 00:00:00.000                                20.00                2009-02-21 00:00:00.000

1           2009-03-05 00:00:00.000                                50.00                2009-02-21 00:00:00.000

1           2009-03-04 00:00:00.000                                25.00                2008-12-19 00:00:00.000

1           2009-02-21 00:00:00.000                                48.00                NULL

1           2009-02-10 00:00:00.000                                12.00                NULL

1           2008-12-19 00:00:00.000                                36.00                NULL

2           2009-03-18 00:00:00.000                                36.00                2009-03-05 00:00:00.000

2           2009-03-17 00:00:00.000                                12.00                2009-02-22 00:00:00.000

2           2009-03-16 00:00:00.000                                22.23                2009-02-11 00:00:00.000

2           2009-03-05 00:00:00.000                                45.00                2008-08-19 00:00:00.000

2           2009-02-22 00:00:00.000                                22.65                2008-03-19 00:00:00.000

2           2009-02-11 00:00:00.000                                12.00                2008-03-19 00:00:00.000

2           2008-08-19 00:00:00.000                                23.00                NULL

2           2008-03-19 00:00:00.000                                65.00                NULL

*/