SQL Server:按一列和另一列值选择不同 [英] SQL Server : select distinct by one column and by another column value

问题描述

这是SQL Server表的数据

This is a SQL Server table's data

id   user_id     start_date   status_id    payment_id
======================================================
2      4         20-nov-11         1          5
3      5         23-nov-11         1         245
4      5         25-nov-11         1         128
5      6         20-nov-11         1         223
6      6         25-nov-11         2         542
7      4         29-nov-11         2         123
8      4         05-jan-12         2         875

我需要通过user_id以及order by id asc来获得不同的值，但是只有一个user_id的起始日期最高

I need to get distinct values by user_id also order by id asc, but only one user_id with highest start_date

我需要以下输出:

id   user_id     start_date   status_id    payment_id
======================================================
8      4         05-jan-12         2         875
4      5         25-nov-11         1         128
6      6         25-nov-11         2         542

请帮助！

什么是SQL查询?

推荐答案

您可以在子查询或CTE中使用row_number().

You can use row_number() in either a sub-query or using CTE.

子查询版本:

select id, user_id, start_date, status_id, payment_id
from
(
  select id, user_id, start_date, status_id, payment_id, 
    row_number() over(partition by user_id order by start_date desc) rn
  from yourtable
) src
where rn = 1

请参见带有演示的SQL提琴

CTE版本:

;with cte as
(
  select id, user_id, start_date, status_id, payment_id, 
    row_number() over(partition by user_id order by start_date desc) rn
  from yourtable
)
select id, user_id, start_date, status_id, payment_id
from cte
where rn = 1

请参见带有演示的SQL提琴

或者您可以将表自身连接起来:

Or you can join the table to itself:

select t1.id, 
  t1.user_id, 
  t1.start_date, 
  t1.status_id, 
  t1.payment_id
from yourtable t1
inner join 
(
  select user_id, max(start_date) start_date
  from yourtable
  group by user_id
) t2
  on t1.user_id = t2.user_id
  and t1.start_date = t2.start_date

请参见带有演示的SQL提琴

所有查询将产生相同的结果:

All of the queries will produce the same result:

| ID | USER_ID |                      START_DATE | STATUS_ID | PAYMENT_ID |
---------------------------------------------------------------------------
|  8 |       4 |  January, 05 2012 00:00:00+0000 |         2 |        875 |
|  4 |       5 | November, 25 2011 00:00:00+0000 |         1 |        128 |
|  6 |       6 | November, 25 2011 00:00:00+0000 |         2 |        542 |

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