筛选后的结果想要这样的
LICESE TIME LON LAT STAT ROWNM ID
-------------------- ----------- ---------- --------- ---- ---------- ----------
陕AT0005 2011/6/1 0:0:03 108.862945 34.233294 5 10024594 168
陕AT0005 2011/6/1 0:2:01 108.862950 34.233296 5 10021976 169
陕AT0005 2011/6/1 0:5:11 108.862963 34.233311 5 10018926 170
陕AT0005 2011/6/1 0:6:34 108.862246 34.233229 4 10055724 171
float a=10.761,b=3.216;
int i,j ;
int g=0;
for(i=1;a<10.989;i++)
{
a=a+0.01;
for(j=1;b<3.307;j++)
{
b=b+0.01;
}
g=g+1;
}这个用sql怎么写呢,还想把每一个i,j,g建一个表保存下来
set @i = 1
set @g = 1
while @i < 10.989
begin
--你要的代码
set @j = 1
while @j < 3.307
begin
--你要的代码
set @j = @j + 1
end
set @i = @i + 1
end
DROP TABLE test
GO
CREATE TABLE test(licese NVARCHAR(10) , tim1 datetime)
GOINSERT INTO test
SELECT N'陕AT0005 ' , '2011/6/1 0:0:03' UNION ALL
SELECT N'陕AT0005 ' , '2011/6/1 0:2:01' UNION ALL
SELECT N'陕AT0005 ' , '2011/6/1 0:5:11' UNION ALL
SELECT N'陕AT0005 ' , '2011/6/1 0:6:34' UNION ALL
SELECT N'陕AT0005 ' , '2011/6/1 0:10:12' UNION ALL
SELECT N'陕AT0005 ' , '2011/6/1 0:15:01'
-----执行查询---------
--
;WITH a AS (
SELECT licese , tim1 , lag(tim1 , 1 , tim1) OVER(ORDER BY tim1) AS tim2 FROM test)SELECT *,DATEPART(mi,tim1) - DATEPART(mi,tim2) FROM a WHERE DATEPART(mi,tim1) - DATEPART(mi,tim2) <= 3 /*执行结果
licese tim1 tim2
---------- ----------------------- ----------------------- -----------
陕AT0005 2011-06-01 00:00:03.000 2011-06-01 00:00:03.000 0
陕AT0005 2011-06-01 00:02:01.000 2011-06-01 00:00:03.000 2
陕AT0005 2011-06-01 00:05:11.000 2011-06-01 00:02:01.000 3
陕AT0005 2011-06-01 00:06:34.000 2011-06-01 00:05:11.000 1(4 行受影响) */
第一行这样处理貌似有点问题,高手指点一下第一行怎么处理好
-----执行查询---------
--
;WITH a AS (
SELECT licese , tim1 , lag(tim1 , 1 ) OVER(ORDER BY tim1) AS tim2 , lead(tim1 , 1) OVER(ORDER BY tim1) AS tim3 FROM test)SELECT * FROM a WHERE abs(DATEPART(mi,tim1) - DATEPART(mi,case when tim2 IS NULL then tim3 ELSE tim2 end)) <= 3 /*执行结果
licese tim1 tim2 tim3
---------- ----------------------- ----------------------- -----------------------
陕AT0005 2011-06-01 00:00:03.000 NULL 2011-06-01 00:02:01.000
陕AT0005 2011-06-01 00:02:01.000 2011-06-01 00:00:03.000 2011-06-01 00:05:11.000
陕AT0005 2011-06-01 00:05:11.000 2011-06-01 00:02:01.000 2011-06-01 00:06:34.000
陕AT0005 2011-06-01 00:06:34.000 2011-06-01 00:05:11.000 2011-06-01 00:10:12.000(4 行受影响) */
name time stat
-------- ------------- ----
a 0:0:01 4
a 0:0:12 4
a 0:0:45 4
a 0:01:03 5
a 0:2:01 4
a 0:2:45 4
a 0:3:35 5
a 0:4:04 5结果显示为:
name time stat id ordr
-------- ------------- ---- ---- ------
a 0:0:01 4 1 1
a 0:0:12 4 1 2
a 0:0:45 4 1 3
a 0:2:01 4 2 1
a 0:2:45 4 2 2
更多 0