如何正确设置查询以在sql中获得特定结果

643ylb08  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(256)

我需要做两个报告从表我显示如下。
我需要一个报告,看看所有的孩子,仍然在学校在执行报告的时刻
我需要一份报告,告诉我每个孩子在学校的时间。对于还在上学的孩子,他们入学时的学时数和当前时间。
你能帮我把这两个案子的逻辑安排妥当吗?
详细说明:
例如,约翰从家里出发,有时去学校,几分钟后他去图书馆(学校->图书馆)。过了一段时间,他又从(图书馆->学校)走了一遍,最后又从(学校->房子)走了一遍。其他人也一样。
现在我很难设置逻辑来获取这两个案例并正确设置查询。
提前感谢:)

IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp
GO

CREATE TABLE #temp 
(
    Person nvarchar(max), 
    src_location nvarchar(max),  
    dst_location nvarchar(max), 
    [time] datetime 
)
GO

INSERT INTO #temp (person, src_location, dst_location, time) 
VALUES ('John', 'House', 'School', '2018-08-31 08:53:55'),
       ('Peter', 'House', 'School', '2018-08-31 09:01:55'),
       ('John', 'School', 'Library', '2018-08-31 09:03:14'),
       ('Tina', 'Park', 'School', '2018-08-31 09:10:22'),
       ('John', 'Library', 'School', '2018-08-31 09:50:14'),
       ('John', 'School', 'House', '2018-08-31 00:48:14'),
       ('Tina', 'School', 'House', '2018-08-31 00:15:22'),
       ('John', 'House', 'School', '2018-10-16 08:22:32')

SELECT * FROM #temp
qyuhtwio

qyuhtwio1#

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

y0u0uwnf

y0u0uwnf2#

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

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

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

ttcibm8c

ttcibm8c3#

然后您可以尝试:

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;

没有试过,对不起,我失业了:)

相关问题