第一次写博文，看了那么多的好文章，还是打算自己也写写，肯定有很多地方不好，欢迎朋友们光临指导。
题目如下：
我有一个数据库t_message 
我想得到类似微信消息那样:
显示 结果为 :
发送者4, 发送2条 最近一条内容为'不要紧吧'
发送者5, 发送1条 最近一条内容为'你是谁'
就是 把所有最近给我发信息的人 列出来 条件是isread =0 toid=3
并且 发多条信息的人只显示最近一条. 同时显示出这个人发了几条
我的解题步骤：
--create a test table t_message
create table t_message
(
id int ,
fromid int,
toid int,
mescontent varchar(100),
sendtime datetime
)
--insert some records into table t_message
insert into t_message
select 1,4,3,'How are you','2014-12-05 17:17:31.690'
union
select 2,4,3,'Are you ok?','2014-12-05 17:17:43.980'
union
select 3,5,3,'Who are you?','2014-12-05 17:20:00.653'
--using common table expression cte to select out mescontent
;with cte as
(
select fromid,mescontent,row_number()over(partition by fromid order by sendtime desc) as row_id from t_message
)
,
b as --common table expression b to select out fromid,and counts of records for each fromid
(
select fromid,count(mescontent) as count_message
from t_message group by fromid
)
Original results:
--selected results:
select b.fromid,b.count_message,a.mescontent
from b inner join (select fromid,mescontent,row_id from cte where row_id=1) a on b.fromid=a.fromid
Results:
