我不太精通mssql。解决第一个问题的一般方法是查询每个用户的最新条目（按[person]分组），然后筛选dst\u location等于school的人（[dst\u location]=“school”）
我看到这并不完全有效，因为它仍然返回蒂娜，而她已经离开学校，看到了吗http://sqlfiddle.com/#!2014年10月18日

我为第一个案例找到了一个很好的解决方案：
包含以下所有信息的表格：

Person      src_location         dst_location              time                   
----------- -------------------- ------------------------- -----------------------
John        House                School                    2018-08-31 08:53:55.000
Peter       House                School                    2018-08-31 09:01:55.000
John        School               Library                   2018-08-31 09:03:14.000
Tina        Park                 School                    2018-08-31 09:10:22.000
John        Library              School                    2018-08-31 09:50:14.000
John        School               House                     2018-08-31 00:48:14.000
Tina        School               House                     2018-08-31 11:15:22.000
John        House                School                    2018-10-16 08:22:32.000

按“person”分区并按“time”排序desc并获得第一个结果（因为order by time dsc in out case是每个人的最后一个结果）。

WITH KidsAtSchool AS (
SELECT m.person, 
       m.src_location,
       m.dst_location,
       m.time,
       DATEDIFF(day, [time], GETDATE()) AS HoursAtSchool,
       ROW_NUMBER() OVER (PARTITION BY person ORDER BY time DESC) AS rn
  FROM #temp AS m
)

SELECT *
  FROM KidsAtSchool
 WHERE rn = 1 
   AND dst_location = 'School'

我得到的结果是正确的：）

Person      src_location         dst_location              time                    HoursAtSchool  
----------- -------------------- ------------------------- ----------------------- --------------
John        House                School                    2018-10-16 08:22:32.000   661
Peter       House                School                    2018-08-31 09:01:55.000   707

如果您仍然找到一个更好的方法或查询，执行速度更快（因为这是一个相当慢），我将感谢阅读您的答案。
另外，第二个案子对我来说仍然很难。

然后您可以尝试：

DECLARE @person nvarchar(50), @location nvarchar(50),  
@time datetime, @permanence int, @enters datetime, @exits datetime,
@inside int, @person_sv nvarchar(50);  

SET @inside = 0
DECLARE school CURSOR FOR   
SELECT person, dst_location, time  
FROM #temp  
ORDER BY person, time DESC;  

OPEN school  

SET @person_sv = ''

FETCH NEXT FROM school   
INTO @person, @location, @time  

WHILE @@FETCH_STATUS = 0  
BEGIN  
     IF @person <> @person_sv -- person changed, we had the time!
     BEGIN
         IF @permanence <> 0 -- only person who had been at school
         BEGIN
             IF @location = 'School' -- last location for person, he/she is still in
             BEGIN
                 @permanence += DATEDIFF(h, @enters, GETDATE()
             END
             INSERT INTO #temp2 VALUES (@person_sv, @permanence)
         END 
         SET @person_sv = @person
         SET @permanence = 0
     END
     IF @location = 'School' -- person enters
     BEGIN
         IF @inside = 0 -- person is not in school
         BEGIN
             set @enters = @time
             set @inside = 1 -- now person is in
         END
     END 
     IF @location <> 'School'
     BEGIN
         IF @inside = 1 -- person is in school
         BEGIN
             set @exits = @time
             set @permanence += DATEDIFF(day, @enters, @exits)
             set @inside = 0 -- person had exited
         END
     END 

FETCH NEXT FROM school   
INTO @person, @location, @time  
END   
CLOSE school;  
DEALLOCATE school;

没有试过，对不起，我失业了：）