根据具有共享数据的某些列创建标识符 [英] Create identifier based on some of the columns with shared data

问题描述

在下面的示例中，sequence 列应该是在某些列中共享相同值的行的标识符 - compania、hrEntr、hrSaida、durJornada、durInterv、iniInterv、termInterv, sistema_horario, turno - 但是它不应该计算其中一列 - dia.

In the example below the column sequence should be a identifier for rows that share the same value in some of the columns - compania, hrEntr, hrSaida, durJornada, durInterv, iniInterv, termInterv, sistema_horario, turno - however it should not take into calculation one of the columns - dia.

如图所示，前五行共享这些列，所以 sequence 应该是 1.第 6 行，不共享所有以前的值应该有它自己的 序列编号设置为2.

So as described in the picture the first five rows share these columns so the sequence should be 1. Row 6, which doesn't share all of the previous values should have it's own sequence number set to 2.

我使用过 ROW_NUMBER() OVER (PARTITION BY...) 但它产生了相反的结果，即当匹配的列停止时它重新开始.

I have worked with ROW_NUMBER() OVER (PARTITION BY... but it creates the opposite result i.e. it starts over when the matching columns stop.

有没有办法创建我想要的结果?

Is there a way to create my desired result?

推荐答案

您可以使用 RANK() 函数.检查这是否满足您的需求:

you can use RANK() function. Check if this solve your need:

drop table if exists stackoverflowTbl;
/********************************************************** DDL+DML */
create table stackoverflowTbl(id int identity (1,1), txt int)
GO
insert stackoverflowTbl (txt) values (1),(1),(2),(1),(3),(22),(22)
GO
select * from stackoverflowTbl
GO
/********************************************************** solution */
select id,txt,
    ROW_Number () OVER (order by txt) - RANK ( ) OVER ( partition by txt order by id ) as MySequence 
from stackoverflowTbl
GO

这篇关于根据具有共享数据的某些列创建标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！