SQL Server选择不同的最新值 [英] SQL Server select distinct latest values

问题描述

我有一个表，其中有很多行(> 10K).大多数行具有与用户名相关的重复的角色值.

I have a table that has a ton of rows (>10K). Most of the rows have duplicate role values associated with the username.

我要尝试的是按 request_id 添加的不同且最新的角色选择行. 我几乎拥有它，但是让我大吃一惊的是，某些 request_id 字段中有null值，因为这些请求是在添加该列之前发出的.我仍然需要将它们包括在select statement中，以防用户自更新以来未输入其他请求.

What I am trying to do is select rows by distinct AND latest role added by request_id. I almost have it, but the part that is kicking my tail is there are null values in some of the request_id fields because those requests were made before that column was added. I STILL need to include them in the select statement in case a user has not entered another request since the update.

以下是我的表格结构示例:

Here a example of my table structure:

 id | uname  | role    | request_id
 0  | jsmith  | User   | null
 1  | jsmith  | Admin   | null
 2  | jsmith  | Dude    | null
 3  | jsmith  | Admin   | 56
 4  | jsmith  | Dude    | 56
 5  | jsmith  | Admin   | 57
 6  | jsmith  | Dude    | 57

这将是期望的结果:

This would be the desired result:

0  | jsmith  | User    | null
5  | jsmith  | Admin   | 57
6  | jsmith  | Dude    | 57

以下是我到目前为止尝试过的语句:

Here are the statements I've tried so far:

select distinct a.uname, a.role, a.request_id from (
    select * from  das_table 
    ) 
b join das_table a on b.role = a.role and b.request_id = a.request_id
where a.uname = 'jsmith'

结果:这会消除具有 request_id = NULL

Result: This eliminates the rows with request_id = NULL

我猜这对我不起作用，因为MAX()不计算null值?

This didn't work for me I guess because MAX() doesn't count null values?

select distinct uname, role, max(request_id) as request_id from das_table where uname='jsmith'
group by uname, role, request_id

我看过的类似问题:

我认为与我研究的其他问题不同的一个警告是request_id可能为空.

从1列中选择不同的值 | SQL Server:按一列选择并通过另一个列值 | SQL Server选择不同的地狱

推荐答案

max查询不起作用，因为您在分组中包含了request_id-尝试:

The max query didn't work because you included request_id in the grouping - try:

select distinct uname, role, max(request_id) as request_id 
from das_table where uname='jsmith'
group by uname, role

SQLFiddle 此处.

SQLFiddle here.

这篇关于SQL Server选择不同的最新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！