SQL Server : group by consecutive

问题:

I have this table:

CREATE TABLE yourtable 
(
    HevEvenementID INT,
    HjvNumeSequJour INT,
    HteTypeEvenID INT
);

INSERT INTO yourtable 
VALUES (12074, 1, 66), (12074, 2, 66), (12074, 3, 5),
       (12074, 4, 7), (12074, 5, 17), (12074, 6, 17),
       (12074, 7, 17), (12074, 8, 17), (12074, 9, 17), (12074, 10, 5)

I need to group by consecutive HteTypeEvenID. Right now I am doing this:

SELECT
    HevEvenementID,
    MAX(HjvNumeSequJour) AS HjvNumeSequJour,
    HteTypeEvenID
FROM
    (SELECT
         HevEvenementID,
         HjvNumeSequJour,
         HteTypeEvenID
     FROM
         yourtable y) AS s 
GROUP BY
    HevEvenementID, HteTypeEvenID
ORDER BY
    HevEvenementID,HjvNumeSequJour, HteTypeEvenID

which returns this:

HevEvenementID  HjvNumeSequJour HteTypeEvenID
---------------------------------------------
12074            2              66
12074            4               7
12074            9              17
12074           10               5

I need to group by consecutive HteTypeEvenID, to get this result:

HevEvenementID  HjvNumeSequJour HteTypeEvenID
----------------------------------------------
12074           2               66
12074           3                5
12074           4                7
12074           9               17
12074           10               5

Any suggestions?

回答1:

In SQL Server, you can do this with aggregation and difference of row numbers:

select HevEvenementID, HteTypeEvenID,
       max(HjvNumeSequJour)
from (select t.*,
             row_number() over (partition by HevEvenementID order by HjvNumeSequJour) as seqnum_1,
             row_number() over (partition by HevEvenementID, HteTypeEvenID order by HjvNumeSequJour) as seqnum_2
      from yourtable t
     ) t
group by HevEvenementID, HteTypeEvenID, (seqnum_1 - seqnum_2)
order by max(HjvNumeSequJour);

I think the best way to understand how this works is by staring at the results of the subquery. You will see how the difference between the two values defines the groups of adjacent values.