选择要选择的重复值

I have a table with a datetime, value and user. This table has multiple rows for the same datetime but with a different user and value.

我有一个具有datetime、value和user的表。这个表有多个行用于相同的datetime，但是有不同的用户和值。

I want select distinct datetime with the corresponding value and user. Where there is a duplicate datetime with different users, the value that user2 has input should be prioritised.

我想要选择具有相应值和用户的不同的datetime。如果有一个重复的datetime与不同的用户，那么user2的输入值应该优先排序。

Table 1
-----------------
DateTime|    Value|    User
--------|---------|---------
1/1/17  |       10|    User1
2/1/17  |       30|    User1
3/1/17  |       10|    User1
1/1/17  |       90|    User2
2/1/17  |       80|    User2

So from the above, I would end up with

所以从上面，我最终会得到。

1/1/17   |     90| User2
2/1/17   |     80| User2
3/1/17   |     10| User1

I'm sure there is a simple answer to this but I can't for the life of me work out how to do it!

我相信有一个简单的答案，但我无法为我的生活想出如何去做!

Any help greatly appreciated.

任何帮助深表感谢。

Thanks

谢谢

4 个解决方案

#1

Not quite simple! Using window functions and common table expressions

不是很简单!使用窗口函数和通用表表达式

; with x as (
select [DateTime], value, [User], row_num = row_number() over(partition by [DateTime] order by [User] desc) from Table1 
)
select x.* from x where row_num = 1

#2

This will always prioritize the input from 'User2', even when there is input from 'User1' and 'User3'.

这将始终优先考虑来自“User2”的输入，即使有来自“User1”和“User3”的输入。

;with cte as (
  select *
  , rn = row_number() over (
      partition by [DateTime]
      order by (case when [user] = 'User2' then 0 else 1 end) asc
      )
  from t
)
select * 
from cte 
where rn=1

rextester http://rextester.com/AZVA85684

results:

结果:

+----------+-------+-------+----+
| DateTime | value | user  | rn |
+----------+-------+-------+----+
| 1/1/17   |    90 | User2 |  1 |
| 2/1/17   |    80 | User2 |  1 |
| 3/1/17   |    10 | User1 |  1 |
+----------+-------+-------+----+

#3

DECLARE @T as table
(
    [DateTime] nvarchar(100),
    VALUE INT,
    [USER] VARCHAR(32)
)

INSERT INTO @T 
VALUES
('1/1/17', 10, 'User1'),
('2/1/17', 30, 'User1'),
('3/1/17', 10, 'User1'),
('1/1/17', 90, 'User2'),
('2/1/17', 80, 'User2')

SELECT t.[DateTime], t.VALUE, t.[USER]
FROM @T t
    JOIN (
        SELECT [DateTime], MAX([USER]) AS [USER] 
        FROM @T 
        GROUP BY [DateTime]
    ) u ON u.[DateTime] = t.[DateTime] AND u.[USER] = t.[USER]
ORDER BY VALUE DESC

#4

;with cte
as
(
select 
*,
row_number() over (partition by date order by replace(user,'user','') desc) as rownum
from
#temp
)
select * from cte where rownum=1

#1