|
第一次写博文，看了那么多的好文章，还是打算自己也写写，肯定有很多地方不好，欢迎朋友们光临指导。
题目如下：
我有一个数据库t_message? 
我想得到类似微信消息那样:
显示?结果为???:??
发送者4,?发送2条?最近一条内容为'不要紧吧'?
发送者5,?发送1条?最近一条内容为'你是谁'?
就是?把所有最近给我发信息的人?列出来?条件是isread?=0?toid=3
并且?发多条信息的人只显示最近一条.?同时显示出这个人发了几条?
我的解题步骤：
--create?a?test?table?t_message
create?table?t_message
(
?id?int?,
?fromid?int,245)">?toid?int,245)">?mescontent?varchar(100),245)">?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,'Are?you?ok?','2014-12-05?17:17:43.980'
select?3,5,'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
,245)">b?as????????????????--common?table?expression?b?to?select?out?fromid,and?counts?of?records?for?each?fromid
from?t_message?group?by?fromid
Original?results:
--selected?results:
select?b.fromid,b.count_message,a.mescontent
from?b?inner?join?(select?fromid,row_id?from?cte?where?row_id=1)?a?on?b.fromid=a.fromid
Results:
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
