T-sql Zresetuj numer wiersza na Field Change

Question

Nov 15, 2012, 09:04 PM

sql-server-2005 row-number tsql sql-server reset

T-sql Zresetuj numer wiersza na Field Change

Przez trzy pełne dni borykałem się z tym problemem i nie mogę się tym zająć. Jest całkiem podobny do mojego ostatniego postu „t-sql sequential duration”, ale nie do końca… Chcę zresetować numer wiersza na podstawie zmiany w kolumnie x (w moim przypadku kolumna „who”)…

Oto pierwsza kwerenda, która zwraca małą próbkę danych surowych (ish):

SELECT      DISTINCT chr.custno, 
            CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as  moddate, 
            chr.who     
FROM        <TABLE> chr 
WHERE       chr.custno = 581827
            AND LEFT(chr.who, 5) = 'EMSZC'
            AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
ORDER BY    chr.custno

Wynik:

custno      moddate             who
581827      2012-11-08 08:38:00.000     EMSZC14
581827      2012-11-08 08:41:10.000     EMSZC14
581827      2012-11-08 08:53:46.000     EMSZC14
581827      2012-11-08 08:57:04.000     EMSZC14
581827      2012-11-08 08:58:35.000     EMSZC14
581827      2012-11-08 08:59:13.000     EMSZC14
581827      2012-11-08 09:00:06.000     EMSZC14
581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1
581827      2012-11-08 09:05:04.000     EMSZC49
581827      2012-11-08 09:06:32.000     EMSZC49
581827      2012-11-08 09:12:03.000     EMSZC49
581827      2012-11-08 09:12:38.000     EMSZC49
581827      2012-11-08 09:14:18.000     EMSZC49
581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

Drugim krokiem jest dodanie numeru wiersza (nie zrobiłem tego w pierwszym zapytaniu z powodu użycia słowa DISTINCT); więc…

WITH c1 AS (
        SELECT      DISTINCT chr.custno
                    CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
                    chr.who
        FROM        <TABLE> chr 
        WHERE       chr.custno = 581827
                    AND LEFT(chr.who, 5) = 'EMSZC'
                    AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
        )

SELECT  ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
FROM    c1

Wynik:

RowID   custno      moddate                      who
1       581827      2012-11-08 08:38:00.000     EMSZC14
2       581827      2012-11-08 08:41:10.000     EMSZC14
3       581827      2012-11-08 08:53:46.000     EMSZC14
4       581827      2012-11-08 08:57:04.000     EMSZC14
5       581827      2012-11-08 08:58:35.000     EMSZC14
6       581827      2012-11-08 08:59:13.000     EMSZC14
7       581827      2012-11-08 09:00:06.000     EMSZC14
8       581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1
9       581827      2012-11-08 09:05:04.000     EMSZC49
10      581827      2012-11-08 09:06:32.000     EMSZC49
11      581827      2012-11-08 09:12:03.000     EMSZC49
12      581827      2012-11-08 09:12:38.000     EMSZC49
13      581827      2012-11-08 09:14:18.000     EMSZC49
14      581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

Następnym krokiem jest to, gdzie utknąłem: celem jest zresetowanie RowID do 1 przy każdej zmianie wartości w kolumnie „kto”. Poniższy kod otrzymuje wynik „prawie tam” (należy zauważyć, że skradłem / pożyczyłem ten kod skądś, ale teraz nie mogę znaleźć strony):

WITH c1 AS (
        SELECT      DISTINCT chr.custno,
                    CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
                    chr.who
        FROM        <TABLE> chr 
        WHERE       chr.custno = 581827
                    AND LEFT(chr.who, 5) = 'EMSZC'
                    AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
        )
, c1a AS    (
            SELECT  ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
            FROM    c1
            )

SELECT  x.RowID - y.MinID + 1 AS Row,
        x.custno, x.Touch, x.moddate, x.who      
FROM    (
            SELECT  custno, who, MIN(RowID) AS MinID
            FROM    c1a
            GROUP BY custno, who
        ) AS y
        INNER JOIN c1a x ON x.custno = y.custno AND x.who = y.who

Wynik:

Row custno      moddate                    who
1   581827      2012-11-08 08:38:00.000     EMSZC14
2   581827      2012-11-08 08:41:10.000     EMSZC14
3   581827      2012-11-08 08:53:46.000     EMSZC14
4   581827      2012-11-08 08:57:04.000     EMSZC14
5   581827      2012-11-08 08:58:35.000     EMSZC14
6   581827      2012-11-08 08:59:13.000     EMSZC14
7   581827      2012-11-08 09:00:06.000     EMSZC14
1   581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1 (Hooray! It worked!)
2   581827      2012-11-08 09:05:04.000     EMSZC49
3   581827      2012-11-08 09:06:32.000     EMSZC49
4   581827      2012-11-08 09:12:03.000     EMSZC49
5   581827      2012-11-08 09:12:38.000     EMSZC49
6   581827      2012-11-08 09:14:18.000     EMSZC49
14  581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1 (Crappies.)

Pożądany rezultat:

Row custno      moddate                     who
1   581827      2012-11-08 08:38:00.000     EMSZC14
2   581827      2012-11-08 08:41:10.000     EMSZC14
3   581827      2012-11-08 08:53:46.000     EMSZC14
4   581827      2012-11-08 08:57:04.000     EMSZC14
5   581827      2012-11-08 08:58:35.000     EMSZC14
6   581827      2012-11-08 08:59:13.000     EMSZC14
7   581827      2012-11-08 09:00:06.000     EMSZC14
1   581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1 
2   581827      2012-11-08 09:05:04.000     EMSZC49
3   581827      2012-11-08 09:06:32.000     EMSZC49
4   581827      2012-11-08 09:12:03.000     EMSZC49
5   581827      2012-11-08 09:12:38.000     EMSZC49
6   581827      2012-11-08 09:14:18.000     EMSZC49
1   581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

Każda pomoc jest mile widziana. Możesz też chichotać na mnie, ponieważ jestem pewien, że jest to dość proste do rozwiązania - po prostu nie mogę wyjść z własnej drogi.

Dzięki.