Server:在表中查找两列不连续的行
Server:在表中查找两列不连续的行
提问于 2016-10-28 20:38:46
我有一张有如下数据的表格：
pK Customer DateTime1 DateTime2
1 6 2016-04-01 00:00:00.000 2016-10-09 00:00:00.000
2 6 2016-07-01 00:00:00.000 2016-10-21 00:00:00.000
3 6 2016-10-01 00:00:00.000 2016-10-20 00:00:00.000我希望找到行，当DateTime1被排序时，相应的DateTime2值(当在customer上过滤时)不遵循相同的顺序。
因此，在上面的例子中，我想找到pK 3的行，因为当DateTime1被命令升序时，DateTime2不会大于第2行中的DateTime2。
它看起来类似于这个问题，但它处理的是项目的顺序，而不是不等式：TSQL check if specific rows sequence exists
我尝试使用CTE语句的一个版本。
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-10-28 20:53:22
Declare @YourTable table (pK int,Customer int,DateTime1 datetime,DateTime2 datetime)
Insert Into @YourTable values
(1,6,'2016-04-01 00:00:00.000','2016-10-09 00:00:00.000'),
(2,6,'2016-07-01 00:00:00.000','2016-10-21 00:00:00.000'),
(3,6,'2016-10-01 00:00:00.000','2016-10-20 00:00:00.000')
;with cte as (
Select *,Flg=Row_Number() over (Partition By Customer Order By DateTime1) - Row_Number() over (Partition By Customer Order By DateTime2)
From @YourTable
)
Select pK
,Customer
,DateTime1
,DateTime2
From cte
Where Flg>0返回
pK Customer DateTime1 DateTime2
3 6 2016-10-01 00:00:00.000 2016-10-20 00:00:00.000Stack Overflow用户
发布于 2016-10-28 20:41:17
这似乎是row_number()的一个很好的应用。
select t.*
from (select t.*,
row_number() over (partition by customer order by datetime1) as seqnum_1,
row_number() over (partition by customer order by datetime2) as seqnum_2
from t
) t
where seqnum_1 <> seqnum_2;但这将根据全局排序返回所有错误排序的行(本例中为pk 2和3)。
您只需要在给定的行上方向改变的位置。为此，请使用lag()
select t.*
from (select t.*,
lag(datetime1) over (partition by customer order by pk) as prev_dt1,
lag(datetime2) over (partition by customer order by pk) as prev_dt2
from t
) t
where (dt1 > prev_dt1 and dt2 <= prev_dt2) or
(dt1 < prev_dt1 and dt2 >= prev_dt2);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40312898
复制相关文章
相似问题
腾讯云开发者
