DECLARE @timeSpan TABLE
( id int,
intime DATETIME ,
outtime DATETIME )
DECLARE @distance TABLE
( x float, y float )


INSERT INTO @timeSpan
( id,intime,outtime)

SELECT ROW_NUMBER() over (order by att.inTime) ‘rowindex‘,att.inTime,att.outTime
FROM attendance att
inner join Vehicle as veh
on att.employeeid=veh.ID
WHERE intime BETWEEN ‘2014/11/17 0:00:00‘ AND ‘2014/11/17 10:14:00‘ and veh.vehicleDepartmentId=3 and veh.vehicleType=1 and veh.id=-297 and att.occday=20141117
order by att.inTime

declare @count int
declare @currentCount int
declare @intime datetime
declare @outtime datetime
select @count = COUNT(*) from @timeSpan
set @currentCount =0
while @count >@currentCount
begin
set @currentCount =@currentCount+1
select @intime =intime, @outtime =outtime from @timeSpan where @currentCount =id
INSERT INTO @distance
( x,y)
select x,y from (select wp.*,wv.x,wv.y FROM (
SELECT workSiteId,employeeId FROM workSitePass where employeeid=‘-297‘ and sTime BETWEEN @intime and @outtime
) AS wp INNER JOIN workSite as wv
on wp.workSiteId=wv.number
inner join Vehicle as ev on wp.employeeId=ev.id
left join deviceLocMap as dlm on dlm.number=wv.number ) as t1


end

select * from @distance

sql 循环表,获取表中某一字段作为查询语句的条件之一