.net 评论表获取前10个最新的评论内容（用户不同）
请教一条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
谢谢
[解决办法]
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
select * from
(
select max(t.id) as [id] from t group by t.userid
) 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'
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
----
87标题8
73标题7
62标题6
54标题5
21标题2
select top 10 * from T as a where ID=(select max(ID) from T where userid=a.userid) order by ID desc
select top 10 max(ID) as ID from T group by userid order by max(ID) desc
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
