Id InquiryId CompanyId UserId ReplyContent CreateTime ParendId
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
2 12 521 37 第一次回复 2013-09-16 14:33:59.573 0
3 12 3 25 第二次次回复 2013-09-16 14:33:59.573 2
4 12 521 37 第三次次回复 2013-09-16 14:33:59.573 3
5 12 521 37 第三次次回复2 2013-09-16 14:33:59.573 3
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
现在想获得在userid=25的情况下不同InquiryId中最新的那条数据,也就是id最大的那条应该得到的数据为:
Id InquiryId CompanyId UserId ReplyContent CreateTime ParendId
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
2 12 521 37 第一次回复 2013-09-16 14:33:59.573 0
3 12 3 25 第二次次回复 2013-09-16 14:33:59.573 2
4 12 521 37 第三次次回复 2013-09-16 14:33:59.573 3
5 12 521 37 第三次次回复2 2013-09-16 14:33:59.573 3
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
现在想获得在userid=25的情况下不同InquiryId中最新的那条数据,也就是id最大的那条应该得到的数据为:
Id InquiryId CompanyId UserId ReplyContent CreateTime ParendId
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
join ( select max(id) id,InquiryId,UserId from t group by InquiryId,UserId where UserId=25 ) b
on t.InquiryId=a.InquiryId and t.UserId=a.UserId and t.Id=a.Id
from (
select *,rn=ROW_NUMBER() over(order by CreateTime desc) from tb
)t
where rn=1
from (
select *,rn=ROW_NUMBER() over(partition by InquiryId order by CreateTime desc) from tb
)t
where rn=1
union all
select * from tb where id =(select top 1 id from tb where userid=25 order by InquiryId desc)
?
;with cte(Id,InquiryId,CompanyId,UserId,ReplyContent,CreateTime,ParendId) as
(
select 1,1,3,25,'第一次回复','2013-09-16 14:33:59.573',0
union all select 2,12,521,37,'第一次回复','2013-09-16 14:33:59.573',0
union all select 3,12,3,25,'第二次次回复','2013-09-16 14:33:59.573',2
union all select 4,12,521,37,'第三次次回复','2013-09-16 14:33:59.573',3
union all select 5,12,521,37,'第三次次回复2','2013-09-16 14:33:59.573',3
union all select 6,12,3,25,'第四次次回复','2013-09-16 14:33:59.573',4
)
select *
from cte a
where not exists(select 1 from cte b where a.InquiryId=b.InquiryId and b.Id>a.Id)/*
Id InquiryId CompanyId UserId ReplyContent CreateTime ParendId
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
*/
from (
select *,rn=ROW_NUMBER() over(partition by InquiryId order by CreateTime desc) from tb
)t
where rn=1试试
这个应该能符合你的要求,适合SQL Server 2005以后的:
;with cte(Id,InquiryId,CompanyId,UserId,ReplyContent,CreateTime,ParendId) as
(
select 1,1,3,25,'第一次回复','2013-09-16 14:33:59.573',0
union all select 2,12,521,37,'第一次回复','2013-09-16 14:33:59.573',0
union all select 3,12,3,25,'第二次次回复','2013-09-16 14:33:59.573',2
union all select 4,12,521,37,'第三次次回复','2013-09-16 14:33:59.573',3
union all select 5,12,521,37,'第三次次回复2','2013-09-16 14:33:59.573',3
union all select 6,12,3,25,'第四次次回复','2013-09-16 14:33:59.573',4
)select Id,InquiryId,
CompanyId,UserId,
ReplyContent,CreateTime,ParendId
from
(
select *,
ROW_NUMBER() over(partition by InquiryId
order by id desc) as rownum
from cte
)t
where rownum = 1
/*
Id InquiryId CompanyId UserId ReplyContent CreateTime ParendId
1 1 3 25 第一次回复 2013-09-16 14:33:59.573 0
6 12 3 25 第四次次回复 2013-09-16 14:33:59.573 4
*/