.net 评论表获取前10个最新的评论内容（用户不同）

[Netcean]

请教一条SQL查询语句。谢谢
评论表 t
字段
id
userid
subject
如何取出最新的10条记录，userid要不同 ，根据id排序
每个usreid只取一条记录
===============
比如：
id userid subject
1 1 标题1
2 1 标题2
3 2 标题3
4 3 标题4
5 4 标题5
6 2 标题6
7 3 标题7
8 7 标题8
=============
结果
id
8
7
6
5
2

谢谢

[daiyueqiang2045]

create  table t
(
	id int,
	userid int,
	subject varchar(20)
)
insert into t 
select 1,1,'标题1' union all
select 2,1,'标题2' union all
select 3,2,'标题3' union all
select 4,3,'标题4' union all
select 5,4,'标题5' union all
select 6,2,'标题6' union all
select 7,3,'标题7' union all
select 8,7,'标题8'
go

with cte as
(
select top 10 max(t1.id)id,t1.userid
from 
t t1 inner join t t2 on t1.userid=t2.userid
group by t1.userid
)
select id,userid
from
cte
order by id desc

[AAACCCEEEFFFA]

select * from
(
select max(t.id) as [id] from t group by t.userid
) order by [id] desc

[daiyueqiang2045]

create  table t
(
	id int,
	userid int,
	subject varchar(20)
)
insert into t 
select 1,1,'标题1' union all
select 2,1,'标题2' union all
select 3,2,'标题3' union all
select 4,3,'标题4' union all
select 5,4,'标题5' union all
select 6,2,'标题6' union all
select 7,3,'标题7' union all
select 8,7,'标题8'
go


select top 10  * from
(
select max(t.id) as [id] from t group by t.userid
)t
order by [id] desc

[中关村网名]

create  table t
(
    id int,
    userid int,
    subject varchar(20)
)
insert into t 
select 1,1,'标题1' union all
select 2,1,'标题2' union all
select 3,2,'标题3' union all
select 4,3,'标题4' union all
select 5,4,'标题5' union all
select 6,2,'标题6' union all
select 7,3,'标题7' union all
select 8,7,'标题8'

select top 10 *
From t as t1
Where Not exists
(
	Select *
	From t as t2
	Where t2.userid = t1.userid
	And t2.id > t1.id
) 
order by id desc
----
8	7	标题8
7	3	标题7
6	2	标题6
5	4	标题5
2	1	标题2

借用楼上的数据一下。

[习惯性蹭分]

if OBJECT_ID('t') is not null
drop table t
go
create  table t
(
    id int,
    userid int,
    subject varchar(20)
)
insert into t 
select 1,1,'标题1' union all
select 2,1,'标题2' union all
select 3,2,'标题3' union all
select 4,3,'标题4' union all
select 5,4,'标题5' union all
select 6,2,'标题6' union all
select 7,3,'标题7' union all
select 8,7,'标题8'
;with sel as(
select *,ROW=ROW_NUMBER()over(PARTITION by userid order by id desc) from t
)
select top 5 ID from sel where ROW=1 order by id desc

[骑着蜗牛去爬山]

select top 10 * from tb a where not exists(select 1 from tb b where a.userid=b.userid and a.id<b.id) order by id desc

[中国风]

select  top 10 * from T as a where ID=(select max(ID) from T where userid=a.userid) order by ID desc

[中国风]

只取ID时这样用

 select  top 10 max(ID) as ID from T group by userid order by max(ID) desc