z**k
发帖数: 378 | 1 sorry i cannot type chinese at work.
I have a collection of records, ordered by date, the record has two other
attributes, say attr and prev, here's a simple example:
date, attr, prev | i****a
发帖数: 36252 | 2 basic idea is to use row_number and self joining tables. if you use CTE,
here is an example
with cte as
(
select ROW_NUMBER() over (order by tb1.colDate) as rn1, *
from tableTest2 tb1
)
select *
from cte
inner join (select ROW_NUMBER() over (order by colDate) as rn2, * from
tableTest2) tb2
on cte.rn1 = tb2.rn2 - 1 and cte.attr <> tb2.prev
if you don't want to use CTE or not on SQL server 2005 or later, then put
result from the 1st query into a temp table |
|
