获取查询以列出特定ID的特定列的开始和结束值之间和之间的记录 [英] Get a query to list the records that are on and in between the start and the end values of a particular column for the same Id

问题描述

有一个带有列的表:

USE 'table';  
insert into person values   
('11','xxx','1976-05-10','p1'),  
('11','xxx ','1976-06-11','p1'),  
('11','xxx ','1976-07-21','p2'),  
('11','xxx ','1976-08-31','p2'),  

谁能建议我一个查询，以获取有关该人按时间顺序更改的地点的开始和结束日期.

Can anyone suggest me a query to get the start and the end date of the person with respect to the place he changed chronologically.

我写的查询

SELECT PId,Name,min(Start_Date) as sdt, max(Start_Date) as edt, place 
from ** 
group by Place;

仅给出我答案的前两行.有人可以提出查询吗?

only gives me the first two rows of my answer. Can anyone suggest the query??

推荐答案

这不是很漂亮，性能可能很糟糕，但至少它是

This isn't pretty, and performance might be horrible, but at least it works:

select min(sdt), edt, place
from (
  select A.Start_Date sdt, max(B.Start_Date) edt, A.place
  from person A
       inner join person B on A.place = B.place
                          and A.Start_Date <= B.Start_Date
       left join person C on A.place != C.place
                         and A.Start_Date < C.Start_Date
                         and C.Start_Date < B.Start_Date
  where C.place is null
  group by A.Start_Date, A.place
) X
group by edt, place

这个想法是A和B代表所有对行. C将是这两者之间具有不同place的任何行.因此，在C.place is null限制之后，我们知道A和B属于同一范围，即，一个地方的一组行，按时间顺序在它们之间没有其他地方.从所有这些对中，我们要确定那些具有最大范围的对，即那些涵盖所有其他对的对.我们使用两个嵌套的group by查询来实现.内部的将为每个可能的开始日期选择最大的开始日期，而外部的将为每个可能的结束日期选择最小的开始日期.结果是描述同一位置的按时间顺序排列的后续行的最大范围.

The idea is that A and B represent all pairs of rows. C will be any row in between these two which has a different place. So after the C.place is null restriction, we know that A and B belong to the same range, i.e. a group of rows for one place with no other place in between them in chronological order. From all these pairs, we want to identify those with maximal range, those which encompass all others. We do so using two nested group by queries. The inner one will choose the maximal end date for every possible start date, whereas the outer one will choose the minimal start date for every possible end date. The result are maximal ranges of chronologically subsequent rows describing the same place.

这篇关于获取查询以列出特定ID的特定列的开始和结束值之间和之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！