消除行
问题描述:
我有以下几点:消除行
Index dateOfInquiry
649454 2016-02-05
649455 2016-02-05
而且我有此查询:
SELECT COUNT(a.dateOfInquiry) as NumberRecords
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC)
Id, dateOfInquiry
FROM
Table
) a
INNER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC)
Id, dateOfInquiry
FROM
Table
)
b
ON b.ID = a.ID + 1
WHERE ABS(DATEDIFF(d, a.dateOfInquiry, b.dateOfInquiry)) > 14
GROUP BY a.strTransactionID
我试图做的仅仅是每7天退货1项纪录他们存在的时期。所以我想要1在上面的数据集中,但我得到0(它消除了两个记录,因为它们之间没有14天)
而且,如果有更多记录并且行为不是'很正确。如果我在1日,2日,3日等到15日有记录,我仍然只需要1条记录,然后在16日或以后的下一条记录再次开始计数,忽略其他记录,直到差异为止超过14天。
基本上我想把一个记录计为1,然后忽略所有进一步的记录,直到14天结束。
我想另一个样品,结果是2:
Index dateOfInquiry
649454 2016-02-01 <- count
649455 2016-02-12 -ignore (<l4 past 649454)
649456 2016-02-12 -ignore (<l4 past 649454)
649457 2016-02-17 <- count
649458 2016-02-22 -ignore (<l4 past 649457)
649459 2016-02-25 -ignore (<l4 past 649457)
答
使用outer apply()
获得下一个符合条件的行的每一行的id
,并使用递归common table expression在开始和工作启动前进的路:
;with cte as (
select t.id, t.dateOfInquiry, x.next_id
from t
outer apply (
select top 1 next_id = i.id
from t as i
where i.dateOfInquiry > dateadd(day,14,t.dateOfInquiry)
order by dateofInquiry, id asc
) x
)
, r_cte as (
--anchor row(s)/starting row(s)
select
id
, dateOfInquiry
, next_id
from cte t
where not exists (
select 1
from cte as i
where i.id < t.id
)
union all
--recursion starts here
select
c.id
, c.dateOfInquiry
, c.next_id
from cte c
inner join r_cte p
on c.id = p.next_id
)
select id, dateOfInquiry
from r_cte
rextester演示:http://rextester.com/PIMVPM32168
回报:
+--------+---------------+
| id | dateOfInquiry |
+--------+---------------+
| 649454 | 2016-02-01 |
| 649457 | 2016-02-17 |
+--------+---------------+
答
what is the use of index column in you requirement ?
你应该提供真正喜欢的场景。
尝试我的脚本与其他示例data.I使用2 ROW_NUMBER
函数递归CTE.So假设如果没有比这更好的解决方案出现,那么我认为CURSOR
将是一个更好的和可以理解的方法。
declare @t table(Index1 int,dateOfInquiry datetime)
insert into @t VALUES
(649454,'2016-02-01') --<- count
,(649455,'2016-02-12') ---ignore (<l4 past 649454)
,(649456,'2016-02-12') ---ignore (<l4 past 649454)
,(649457,'2016-02-17') --<- count
,(649458,'2016-02-22') ---ignore (<l4 past 649457)
,(649459,'2016-03-02') ---count
,(649459,'2016-03-15') ---ignore (<l4 past 649457)
;WITH CTE
AS (
SELECT min(dateOfInquiry) dateOfInquiry
,cast(0 AS BIGINT) r
FROM @t
UNION ALL
SELECT *
FROM (
SELECT t.dateOfInquiry
,ROW_NUMBER() OVER (
ORDER BY t.dateOfInquiry
) rn1
FROM @t t
INNER JOIN (
SELECT dateOfInquiry
FROM (
SELECT dateOfInquiry
,row_number() OVER (
ORDER BY dateOfInquiry DESC
) rn
FROM cte c1
) c2
WHERE rn = 1
) c1 ON datediff(day, c1.dateOfInquiry, t.dateOfInquiry) >= 14
) t4
WHERE rn1 = 1
)
SELECT *
FROM cte
谢谢,我今天早上要试试这个。如果我想从中看出来,我可以将“With”表作为视图,并用视图替换cte的实例,对吧? –
@PatrickSchomburg是的,只要将'with'移到'r_cte之前('。像这样:http://rextester.com/PLNO30199 – SqlZim