如何根据其他属性值过滤日期范围 [英] How do I filter date range depend on another attribute value
问题描述
我有一个包含顺序和目标日期序列的数据集。我需要在给定的目标日期范围之间检索数据，并且应该包括从头到尾的整个序列顺序。
TNARef ; TargetDate 序列
< b> ---------- &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ------------- &NBSP;&NBSP; -----------
TNA0001 &NBSP; 02/01/2016 1
TNA0001 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0001 &NBSP; 2月7日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0002 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 1
TNA0002 &NBSP; 2月5日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0002 &NBSP; 2月8日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0003 &NBSP; 01 /2016分之26&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 1
TNA0003 &NBSP; 2月1日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0003 &NBSP; 02/03/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0004 &NBSP; 01/20/2016年&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 1
TNA0004 &NBSP; 01 /2016分之26&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0004 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
在我的查询中我可以得到以下结果
目标日期： 02/01/2016至02/10/2016
TNARef TargetDate 序列
---------- &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ------------- &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; -----------
TNA0001 &NBSP; 02/01/2016 1
TNA0001 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0001 &NBSP; 2月7日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0002 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 1
TNA0002 &NBSP; 2月5日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0002 &NBSP; 2月8日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0003 &NBSP; 2月1日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0003 &NBSP; 02/03/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0004 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
但只需要过滤
TNARef TargetDate 顺序
---------- ------- ------ -----------
TNA0001 &NBSP; 02/01/2016 1
TNA0001 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0001 &NBSP; 2月7日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
TNA0002 &NBSP; 02/02/2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 1
TNA0002 &NBSP; 2月5日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2
TNA0002 &NBSP; 2月8日/ 2016&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 3
我怎样才能通过SQL做到这一点？请帮助我们
I have a DATASET with sequence of order and target date. I need to retrieve data in between given target date range and there should include entire sequence order from beginning to end.
TNARef TargetDate Sequence
---------- ------------- -----------
TNA0001 02/01/2016 1
TNA0001 02/02/2016 2
TNA0001 02/07/2016 3
TNA0002 02/02/2016 1
TNA0002 02/05/2016 2
TNA0002 02/08/2016 3
TNA0003 01/26/2016 1
TNA0003 02/01/2016 2
TNA0003 02/03/2016 3
TNA0004 01/20/2016 1
TNA0004 01/26/2016 2
TNA0004 02/02/2016 3
In my query i can get following result
Target Date: 02/01/2016 To 02/10/2016
TNARef TargetDate Sequence
---------- ------------- -----------
TNA0001 02/01/2016 1
TNA0001 02/02/2016 2
TNA0001 02/07/2016 3
TNA0002 02/02/2016 1
TNA0002 02/05/2016 2
TNA0002 02/08/2016 3
TNA0003 02/01/2016 2
TNA0003 02/03/2016 3
TNA0004 02/02/2016 3
But only need to filter
TNARef TargetDate Sequence
---------- ------------- -----------
TNA0001 02/01/2016 1
TNA0001 02/02/2016 2
TNA0001 02/07/2016 3
TNA0002 02/02/2016 1
TNA0002 02/05/2016 2
TNA0002 02/08/2016 3
How can i do that only by SQL? Please help me guys
推荐答案
如果我理解正确，你只想选择整个序列是否在范围内。首先，您需要知道每个参考的最大和最小日期，然后根据它进行过滤。假设您使用的是SQL Server并且有两个名为@FromTargetDate和@ToTargetDate的参数：
If I understand you correctly you want to select only if the entire sequence falls into the range. First you need to know the max and min date for each reference and then filter based on that. Assuming you are using SQL Server and have two parameters named @FromTargetDate and @ToTargetDate:
with cte1 as
(
SELECT TNARef, Min(TargetDate) AS MinDate, Max(TargetDate) AS MaxDate
FROM yourtable
GROUP BY TNARef
)
, cte2 AS
(
SELECT TNARef
FROM cte1
WHERE MinDate >= @FromTargetDate
AND MaxDate <= @ToTargetDate
)
SELECT *
FROM yourtable
WHERE TNARef IN (SELECT TNARef FROM cte2)
当然，查询的结构可以不同并进行优化，但我喜欢使用CTE，因为它们更容易理解。
Of course the query could be structured differently and optimized, but I like to use CTEs because they are easier to follow.
这篇关于如何根据其他属性值过滤日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
