T-sql 在字段更改时重置行号 [英] T-sql Reset Row number on Field Change
问题描述
类似于我最近的一篇文章t-sql 顺序持续时间""，但不完全相同，我想根据 x 列(在我的情况下，列who")中的更改来重置行号.
Similar to a recent post of mine "t-sql sequential duration"", but not exactly the same, I want to reset the row number based on a change in column x (in my case, column "who").
这是返回原始(ish)数据小样本的第一个查询:
Here's the first query that returns the a small sample of the raw(ish) data:
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
结果:
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
第二步是添加行号(我在第一次查询中没有这样做，因为使用了DISTINCT这个词)；所以……
Second step is to add the row number (I didn’t do this in the first query because of the use of the word DISTINCT); so…
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
结果:
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
下一步是我卡住的地方:目标是在who"列中每次更改值时将 RowID 重置为 1.下面的代码得到了一个几乎在那里"的结果(需要注意的是我从某个地方偷/借了这个代码，但现在我找不到网站):
The next step is where I’m stuck: the goal is to reset the RowID to 1 on each change of value in the "who" column. The following code gets an "almost there" result (and it should be noted that I stole/borrowed this code from somewhere, but now I can’t find the website):
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
结果:
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.)
想要的结果:
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
感谢任何帮助.
推荐答案
如果您使用的是 SQL Server 2012，则可以使用 LAG 将值与前一行进行比较，您可以使用 SUM 和 OVER 记录更改.
If you are on SQL Server 2012 you can use LAG to compare value with previous row and you can use SUM and OVER to record the changes.
with C1 as
(
select custno,
moddate,
who,
lag(who) over(order by moddate) as lag_who
from chr
),
C2 as
(
select custno,
moddate,
who,
sum(case when who = lag_who then 0 else 1 end)
over(order by moddate rows unbounded preceding) as change
from C1
)
select row_number() over(partition by change order by moddate) as RowID,
custno,
moddate,
who
from C2
更新:
SQL Server 2005 的一个版本.它使用递归 CTE 和临时表作为您需要迭代的数据的中间存储.
A version for SQL Server 2005. It uses a recursive CTE and a temp table for intermediary storage of the data you need to iterate over.
create table #tmp
(
id int primary key,
custno int not null,
moddate datetime not null,
who varchar(10) not null
);
insert into #tmp(id, custno, moddate, who)
select row_number() over(order by moddate),
custno,
moddate,
who
from chr;
with C as
(
select 1 as rowid,
T.id,
T.custno,
T.moddate,
T.who,
cast(null as varchar(10)) as lag_who
from #tmp as T
where T.id = 1
union all
select case when T.who = C.who then C.rowid + 1 else 1 end,
T.id,
T.custno,
T.moddate,
T.who,
C.who
from #tmp as T
inner join C
on T.id = C.id + 1
)
select rowid,
custno,
moddate,
who
from C
option (maxrecursion 0);
drop table #tmp;
这篇关于T-sql 在字段更改时重置行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
