SQL Server:按一列和另一列值选择不同 [英] SQL Server : select distinct by one column and by another column value
本文介绍了SQL Server:按一列和另一列值选择不同的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是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屋!
查看全文