SQL Case语句返回重复行的索引

I'm running a query to return multiple rows of employee hours data from multiple tables. There is a valid condition of employees putting a day shift of 8 hours per day to multiple customers for billing purposes. I want to update the "Index" column with a unique index for any duplicate across 3 columns

我正在运行查询以从多个表返回多行员工小时数据。员工有一个有效的条件,即每天8小时的班次为多个客户提供计费目的。我想更新“索引”列,并为3列中的任何重复更新唯一索引

eg.

Emp   Hrs   Date   Index
Fred  8     11/7   1
Fred  8     11/7   2
Fred  8     11/7   3
Fred  10    12/7   1
Fred  9     13/7   1

In the Select statement these is a Case statement which returns a value in the Index column for a duplicate but it doesn't give a unique value

在Select语句中,这些是一个Case语句,它在Index列中返回一个重复的值,但它不提供唯一值

case when count(*) over (partition by Emp, Hrs, Date) > 1
    then 1
        else 0
    end

can I include a group by condition in a case statement to achieve this?

我可以在案例陈述中按条件包括一组来实现这一目标吗?

2 个解决方案

#1

Try this:

UPDATE x
SET x.Index = x.Index_Calc
FROM
(
    SELECT Index, ROW_NUMBER() OVER (PARTITION BY Emp, Hrs, Date ORDER BY (SELECT 1)) AS Index_Calc
    FROM yourTable
) x

I based my answer off this Stack Overflow question, but I believe it should work here as well.

我根据Stack Overflow问题得出了答案,但我相信它也适用于此。

#2

You can achieve this with row_number() and partition by.

您可以使用row_number()和partition by来实现此目的。

Partition will give you a row number that resets every time a new "group" of values is encountered.

分区将为您提供一个行号,每次遇到新的“值”组时,该行号都会重置。

create table billings (employee varchar(100), hours decimal(10,2), billed_on date)
go

insert into billings values
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-19'),
('John', 10, '2017-02-01'),
('John', 8, '2017-02-01');

select  employee, hours, billed_on,
        row_number() over (partition by employee, billed_on order by employee) as ix
from    billings
order by billed_on, employee, ix

Produces:

    employee  hours billed_on    ix
1   John      10,00 01.02.2017   1
2   John       8,00 01.02.2017   2
3   Mike       8,00 01.02.2017   1
4   Mike       8,00 01.02.2017   2
5   Mike       8,00 01.02.2017   3
6   Mike       8,00 19.02.2017   1

#1