百分寻求一SQL语句的写法

Question

declare @clbh int
declare aa cursor for 
select 车辆编号  from 车辆全国定位表
open aa
fetch next from aa  into @clbh
while @@fetch_status=0
begin
declare rq datatime,rq1 datetime
declare bb cursor for 
select 定位时间 from 车辆全国定位表 where 车辆编号  =@clbh
open bb
fetch next from bb into @rq
fetc next from bb into @rq1
while @@fetch_status=0
begin 
if datediff(hh,@rq,@rq1)>2 --连续时间
select * from 车辆全国定位表
where 定位时间 =@rq1
fetch next from bb into @rq
fetch next from bb into @rq1
end
close bb
deallocate bb
fetch next from aa  into @clbh
end 
close aa
deallocate aa
不一定对
只是提供一个思路

Answer 1

谢谢ghxghx() 同志，你的代码我正在阅读。另外补充两点：
1.每隔半小时插入的一批记录的数目不是固定的，因车辆数目不确定（每天还在增加）。
2.相邻插入的两批记录中同一车辆的定位时间间隔不能确定为半小时（因通信器材等方面的时延因素）。
3.方便起见，暂将“抛锚”定义为“在某地区停留时间在两小时以上”（同一车辆连续在同一地区出现四次）。各位请继续关注！

Answer 2

想想这个问题还是比较有趣的。试着写了一句sql语句
设表中字段为id,code,area,time,sign分别对应
定位编号、车辆编号、车辆所在地区编号、定位时间、定位有效标志
select a.code,a.area from t1 as a join 
(select code,max(time) as Maxtime from t1 b
where code in (select code from t1 where sign=1)
and area<>(select area from t1 where code=b.code and sign=1)
group by code) as c on a.code=c.code
where a.time > c.Maxtime
group by a.code,a.area
having count(a.id)>= 4实际应用中，可以分步来写，容易理解一些，我的思路是
1.得到一个临时列表，保存不在最近扫描的记录地点的最大时间
select code,max(time) as Maxtime into #temp from t1 a
where code in (select code from t1 where sign=1)
and area<>(select area from t1 where code=a.code and sign=1)
group by code
2.扫描这个表中从此时间到最大时间之间有记录数大于4条的记录
select a.code,a.area from t1 as a join #temp as b on a.code=b.code
where a.time > b.Maxtime
group by a.code,a.area
having count(a.id)>=4 and max(a.sign)=1不过试着用一句sql语句解决问题是比较有趣的。
在上面的一句话中，关于子查询的部分感觉还有一些问题，只是也不知道如何优化才好，希望大家来讨论讨论。

Answer 3

设表中字段为id,code,area,time,sign分别对应
定位编号、车辆编号、车辆所在地区编号、定位时间、定位有效标志
select * from table
where time>=m_time 
and sign='1'
and code in
(
  select code
  from table group by code
  having (count(distinct area)=1)
)m_time是当前时间与车辆抛锚时间之差值。

Answer 4

blackhawk_yps(原来是这样)同志理解错我的意思了

Answer 5

问题存在于：
where time>=m_time 
and sign='1'
语句中。表中的“定位时间”列指的是此次（每半小时一次）通信系统获得该车位置信息的时间 time>=m_time 在这里好象没有什么意义。另外“定位有效标志位”仅用来标识通信系统最后一次（离查询时间最近）向定位表中插入的车辆定位信息，我觉得于查询没有明显关系。谢谢各位的参与。继续！继续！

Answer 6

呵，如果一辆车在开始(2 小时内)第一次在A地，第二第三次信息丢失，第四次在A地，能否认为车已在A地“抛锚”

Answer 7

问题的关键是对“抛锚”定义，按照你的说法，是：“在某地区停留时间在两小时以上（同一车辆连续在同一地区出现四次以上）”。
也就是说，对于上述各位的表结构来说，就是：
Code,Area相同的记录，Time连续出现的次数超过4次。
使用Sign的意义在于可以限制查询结果：只查询当前抛锚的车辆，过去曾经抛锚的不关心。-- 实际上，不使用Sign也行。
那么，思路就出来了：
1、对原表按Code排序，获得每组Code中每条记录按照时间顺序的编号（反序）。排除掉最大编号小于4的（从1开始）。放入临时表#temp中。
2、求出每辆车当前的位置area。（#temp中）
3、在#temp中查找存在area与当前area不同的code,排除之。
4、剩下的就是你需要的。

Answer 8

实现：
1、排序
select *,
          (select count(*)+1
           from t1 b
           where b.code=a.code
             and b.time>a.time
          ) as No
into #temp1
from t1 a2、排除最大编号小于4的（从1开始）：
select code,max(No) as MaxNo
into #temp2
from #temp1
group by codeselect *
into #temp3
from #temp1
where code in(select code from #temp2 where MaxNo>3)3、求出每辆车当前的位置area。（#temp中）
select code,area
into #temp4
from #temp3
where sign=14、在#temp中查找存在area与当前area不同的code,排除之：
select *
from #temp3
where code not in(select code
                  from #temp3
                  where area area not in(select area from #temp4)
                 )

Answer 9

针对刚才几位兄台的问题这里一起给个回复：to xhfjy(峰):
不考虑你说的“如果一辆车在开始(2 小时内)第一次在A地，第二第三次信息丢失，第四次在A地”的情况，意即针对某一辆车来说不存在定位信息不能确定的情况。to: blackhawk_yps(原来是这样):
当前查询要求找到在某段时间（比如一个季度）内的所有抛锚记录，而非查询本次插入（半小时一次,也就是你提到的“当前”）的记录集合中的抛锚记录。
SE1() 同志的理解--
问题的关键是对“抛锚”定义，按照你的说法，是：“在某地区停留时间在两小时以上（同一车辆连续在同一地区出现四次以上）”。也就是说，对于上述各位的表结构来说，就是：Code,Area相同的记录，Time连续出现的次数超过4次。使用Sign的意义在于可以限制查询结果：只查询当前抛锚的车辆，过去曾经抛锚的不关心。-- 实际上，不使用Sign也行。”
是正确的，你可以看看。谢谢各位的参与，你们写的代码我正在阅读。
再次感谢！欢迎各位继续参与！分是不成问题的。 

Answer 10

举个例子，进一步阐明我的意思：假设共有3辆车，车辆编号分别为CAR1，CAR2和CAR3；
假设全国共有2个地区 AREA1 和 AREA2；现在是时间是:2002-04-15 15:20:00；
表中此刻的记录可以描述为：
id         code         area         time                    sign
n          ...          ...          ...                     ...
n+1        CAR1         AREA1        2002-04-15 15:00:00     1
n+2        CAR2         AREA1        2002-04-15 15:00:01     1
n+3        CAR3         AREA2        2002-04-15 15:00:01     1
表中的 time 列为定义:通信系统获得该车定位信息后将信息插入表中的时刻。时间到2002-04-15 15:30:00的时候通信系统又获得三辆车的定位消息，将信息插入表中，插入完成后表的描述如下：
id         code         area         time                    sign
n          ...          ...          ...                     ...
n+1        CAR1         AREA1        2002-04-15 15:00:00     0
n+2        CAR2         AREA1        2002-04-15 15:00:01     0
n+3        CAR3         AREA2        2002-04-15 15:00:01     0
n+4        CAR1         AREA1        2002-04-15 15:30:01     1
n+5        CAR2         AREA2        2002-04-15 15:30:00     1
n+6        CAR3         AREA1        2002-04-15 15:30:01     1
注意: CAR2 和 CAR3 已经不在各自原来（半小时前）的地区，而 CAR1 则还在原来的地区。假设定义连续在一个地区停留时间为一个小时，则 CAR1 此时就被视为抛锚车辆。
若此刻查询抛锚车辆，查询结果应该为：
id         code         area         time                    sign
n+1        CAR1         AREA1        2002-04-15 15:00:00     0
n+1        CAR1         AREA1        2002-04-15 15:30:00     1

Answer 11

第4点错了，应该是：
select *
from #temp3
where code not in(select a.code
                  from #temp3 a, #temp4 b
                  where a.code=b.code
                    and a.area<>b.area
                 )经过测试，达到了要求。

Answer 12

总结之后，写一个SQL完成之：
select *
from (select *
      from t1
      where code in(select code
                    from (select *,
                                 (select count(*)+1
                                  from t1 b
                                  where b.code=a.code
                                    and b.QueryTime>a.QueryTime
                                  ) as No
                          from t1 a) as aa
                    group by code
                    having max(No)>1
                   )
     ) as bb
where code not in(select a1.code
                  from t1 a1, (select code,area
                              from t1
                              where sign=1
                              ) b1
                  where a1.code=b1.code
                    and a1.area<>b1.area
                 )

Answer 13

这是针对mars884813(一帆) 的例子的，其中的QueryTime对应例子中的time。“having max(No)>1”是因为例子里连续两次出现即视为抛锚。