SQL 语句

Question

选手坐标表：  select  '选手1' 编号, 时刻1 检测时间, 0  x坐标 ,0 y坐标 into #
insert into # select '选手1' , '时刻2' , 10  , 10 
insert into # select '选手1' , '时刻3' , 20  , 20  
insert into # select '选手1' , '时刻4' , 30  , 30  
insert into # select '选手1' , '时刻5' , 20  , 20    
insert into # select '选手1' , '时刻6' , 40  , 60   
insert into # select '选手3' , '时刻1' , 10  , 10  
insert into # select '选手3' , '时刻2' , 10  , 10 
insert into # select '选手3' , '时刻3' , 20  , 20  
insert into # select '选手3' , '时刻4' , 30  , 30  
insert into # select '选手3' , '时刻5' , 40  , 40   
insert into # select '选手3' , '时刻6' , 20  , 20   
insert into # select '选手3' , '时刻7' , 9   , 10 
删除规则说明：
1.判断每个选手时刻2与时刻1的距离，若距离小于20，则删除时刻2的数据
编号   检测时间  x坐标   y坐标 
选手1 时刻1 0 0  
选手1 时刻3 20 20
选手1 时刻4 30 30 
选手1 时刻5 20 20       
选手1 时刻6 40 60    
选手3 时刻1 10 10   
选手3 时刻3 20 20   
选手3 时刻4 30 30  
选手3 时刻5 601 80  
选手3 时刻6 9 9
2.判断每个选手时刻3与上一个有效时刻的距离，若距离小于20，则删除时刻3的数据 
编号   检测时间  x坐标   y坐标 
选手1 时刻1 0 0  
选手1 时刻3 20 20
选手1 时刻4 30 30 
选手1 时刻5 20 20       
选手1 时刻6 40 60    
选手3 时刻1 10 10   
选手3 时刻4 30 30  
选手3 时刻5 601 80  
选手3 时刻6 9 9
3.判断每个选手时刻4与上一个有效时刻的距离，若距离小于20，则删除时刻4的数据 
编号   检测时间  x坐标   y坐标 
选手1 时刻1 0 0  
选手1 时刻3 20 20
选手1 时刻5 20 20       
选手1 时刻6 40 60    
选手3 时刻1 10 10   
选手3 时刻4 30 30  
选手3 时刻5 601 80  
选手3 时刻6 9 9 *********按该算法删除所有误差数据**************得出输出结果，并分组编号为： 
编号  检测时间  x坐标   y坐标  分组序号 
选手1 时刻1 0 0 1 
选手1 时刻3 20 20 2 
选手1 时刻6 40 60 3  
选手3 时刻1 10 10 1 
选手3 时刻4 30 30 2
选手3 时刻7 9 10 3 
数据量较大，不考虑用游标

Answer 1

IF OBJECT_ID( 'tempdb..#') IS NOT NULL
DROP TABLE #
go
IF OBJECT_ID( 'tempdb..#1') IS NOT NULL
DROP TABLE #1
go
select  '选手1' 编号, '时刻1' 检测时间, 0  x坐标 ,0 y坐标 into #
union all select '选手1' , '时刻2' , 10  , 10 
union all select '选手1' , '时刻3' , 20  , 20  
union all select '选手1' , '时刻4' , 30  , 30  
union all select '选手1' , '时刻5' , 20  , 20    
union all select '选手1' , '时刻6' , 40  , 60   
union all select '选手3' , '时刻1' , 10  , 10  
union all select '选手3' , '时刻2' , 10  , 10 
union all select '选手3' , '时刻3' , 20  , 20  
union all select '选手3' , '时刻4' , 30  , 30  
union all select '选手3' , '时刻5' , 40  , 40   
union all select '选手3' , '时刻6' , 20  , 20   
union all select '选手3' , '时刻7' , 9   , 10 SELECT a.编号,a.检测时间,MIN(a.x坐标) x坐标,MIN(a.y坐标) y坐标 ,MIN(b.检测时间 ) n检测时间
INTO #1
FROM # a
LEFT JOIN # b
ON a.编号 = b.编号 AND a.检测时间 < b.检测时间 AND (b.x坐标-a.x坐标)*(b.x坐标-a.x坐标)+(b.y坐标 -a.y坐标 )*(b.y坐标 -a.y坐标 )>= 400
GROUP BY a.编号,a.检测时间
ORDER BY a.编号,a.检测时间  
/*
SELECT * FROM #1 a
 WHERE a.检测时间 IN (SELECT n检测时间 FROM #1 WHERE a.编号 = 编号)
 */
 ALTER TABLE #1 ADD flag INT
 go
 
DECLARE @time1 AS VARCHAR(100)
DECLARE @time2 AS VARCHAR(100)
DECLARE @i INT
UPDATE #1 SET flag = @i--CASE WHEN @time1 = 检测时间 OR @time1 IS null  THEN 1 ELSE 0 END
,@i = CASE WHEN @time1 = 检测时间 OR @time1 IS NULL THEN 1 ELSE 0 end
,@time1 = CASE WHEN  @time1 = 检测时间 OR @time1 IS NULL THEN n检测时间 ELSE @time1 end

SELECT 编号,检测时间,x坐标,y坐标 FROM #1
WHERE flag = 1编号    检测时间  x坐标         y坐标         
----- ----- ----------- ----------- 
选手1   时刻1   0           0
选手1   时刻3   20          20
选手1   时刻6   40          60
选手3   时刻1   10          10
选手3   时刻4   30          30
选手3   时刻7   9           10（所影响的行数为 6 行）

Answer 2

   IF OBJECT_ID( 'tempdb..#') IS NOT NULL
    DROP TABLE #
go
IF OBJECT_ID( 'tempdb..#1') IS NOT NULL
    DROP TABLE #1
go
select  '选手1' 编号, '时刻1' 检测时间, 0  x坐标 ,0 y坐标 into #
union all select '选手1' , '时刻2' , 10  , 10 
union all select '选手1' , '时刻3' , 20  , 20  
union all select '选手1' , '时刻4' , 30  , 30  
union all select '选手1' , '时刻5' , 20  , 20    
union all select '选手1' , '时刻6' , 40  , 60 
union all select '选手1' , '时刻7' , 40  , 60   
union all select '选手3' , '时刻1' , 10  , 10  
union all select '选手3' , '时刻2' , 10  , 10 
union all select '选手3' , '时刻3' , 20  , 20  
union all select '选手3' , '时刻4' , 30  , 30  
union all select '选手3' , '时刻5' , 40  , 40   
union all select '选手3' , '时刻6' , 20  , 20   
union all select '选手3' , '时刻7' , 9   , 10 
union all select '选手3' , '时刻8' , 9   , 10 按楼上的，如果多加两行，结果不对

Answer 3

IF OBJECT_ID( 'tempdb..#') IS NOT NULL
    DROP TABLE #
go
IF OBJECT_ID( 'tempdb..#1') IS NOT NULL
    DROP TABLE #1
go
select  '选手1' 编号, '时刻1' 检测时间, 0  x坐标 ,0 y坐标 into #
union all select '选手1' , '时刻2' , 10  , 10 
union all select '选手1' , '时刻3' , 20  , 20  
union all select '选手1' , '时刻4' , 30  , 30  
union all select '选手1' , '时刻5' , 20  , 20    
union all select '选手1' , '时刻6' , 40  , 60 
union all select '选手1' , '时刻7' , 40  , 60   
union all select '选手3' , '时刻1' , 10  , 10  
union all select '选手3' , '时刻2' , 10  , 10 
union all select '选手3' , '时刻3' , 20  , 20  
union all select '选手3' , '时刻4' , 30  , 30  
union all select '选手3' , '时刻5' , 40  , 40   
union all select '选手3' , '时刻6' , 20  , 20   
union all select '选手3' , '时刻7' , 9   , 10 
union all select '选手3' , '时刻8' , 9   , 10 select *,ROW_NUMBER ()over(partition by 编号 order by 检测时间)sn  
into #1
from #;with tb(player,CheckTime,Xco,Yco,SN,IsKeeped,X1,Y1)
AS
(SELECT 编号,检测时间,x坐标,y坐标,SN,1,x坐标,y坐标 FROM #1
WHERE SN=1UNION ALL
SELECT 编号,检测时间,x坐标,y坐标,a.SN,
case when (x坐标-X1)*(x坐标-X1) +(y坐标-Y1 )*(y坐标-Y1 )>400 then 1 else 0 end
,case when (x坐标-X1)*(x坐标-X1) +(y坐标-Y1 )*(y坐标-Y1 )>400 then x坐标 else x1 end
,case when (x坐标-X1)*(x坐标-X1) +(y坐标-Y1 )*(y坐标-Y1 )>400 then y坐标 else y1 end
 FROM #1 A
JOIN tb b on a.编号=b.player and a.sn=b.SN+1
)
select player ,CheckTime ,Xco ,Yco  from tb where IsKeeped =1

Answer 4

--语句一：
;with t1 as(
select 编号,检测时间,x坐标,y坐标 FROM # WHERE 检测时间 in('时刻1','时刻2')
),t2 as(
select 编号,'时刻2' 检测时间,x坐标,y坐标,
(select x坐标 from t1 a where a.编号=b.编号 and a.检测时间='时刻1') x1坐标, 
(select y坐标 from t1 a where a.编号=b.编号 and a.检测时间='时刻1') y1坐标 
from t1 b where 检测时间='时刻2'
),t3 as (
select 编号,'时刻2' 检测时间,x坐标,y坐标 from t2 where (x坐标+y坐标-x1坐标-y1坐标)<=20
)
delete  from # from # a inner join t3 b on a.编号=b.编号 and a.检测时间=b.检测时间
/*
(2 行受影响)*/

Answer 5

--语句二:
;with t1 as(
select row_number() over(partition by 编号 order by cast(replace(检测时间,'时刻','') as int)) cnt,* from #
),t2 as(
select * from t1 where 检测时间='时刻3'
),t3 as(
select a.* from t1 a inner join t2 b on a.编号=b.编号 and (a.cnt=b.cnt or a.cnt+1=b.cnt)
),t4 as(
select 编号,'时刻3' 检测时间,x坐标,y坐标,
(select x坐标 from t3 a where a.编号=b.编号 and a.cnt+1=b.cnt) x1坐标, 
(select y坐标 from t3 a where a.编号=b.编号 and a.cnt+1=b.cnt) y1坐标 
from t3 b where 检测时间='时刻3'
)
delete  from # from # a inner join t4 b on a.编号=b.编号 and a.检测时间=b.检测时间
/*
(2 行受影响)
*/

Answer 6

--更正语句二：
;with t1 as(
select row_number() over(partition by 编号 order by cast(replace(检测时间,'时刻','') as int)) cnt,* from #
),t2 as(
select * from t1 where 检测时间='时刻3'
),t3 as(
select a.* from t1 a inner join t2 b on a.编号=b.编号 and (a.cnt=b.cnt or a.cnt+1=b.cnt)
),t4 as(
select 编号,'时刻3' 检测时间,x坐标,y坐标,
(select x坐标 from t3 a where a.编号=b.编号 and a.cnt+1=b.cnt) x1坐标, 
(select y坐标 from t3 a where a.编号=b.编号 and a.cnt+1=b.cnt) y1坐标 
from t3 b where 检测时间='时刻3'
),t5 as (
select 编号,'时刻3' 检测时间,x坐标,y坐标 from t4 where (x坐标+y坐标-x1坐标-y1坐标)<=20
)
delete  from # from # a inner join t5 b on a.编号=b.编号 and a.检测时间=b.检测时间
/*
(1 行受影响)
*/

Answer 7

要更改SQL SERVER 设置
100是默认设置

Answer 8

MSDN如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。