34,837
社区成员




USE [Tuan]
GO
/****** Object: StoredProcedure [dbo].[Pro_Fujet_SNS_Attention_GetList_SortByNewTopic] Script Date: 08/28/2010 12:34:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Pro_Fujet_SNS_Attention_GetList_SortByNewTopic]
@userid int,
@top int,
@index int
as
begin
select a.Notes,a.AttentionUserID,d.AttentionBody ,
b.Headimg,isnull(b.nickname,Email) as NickName,isnull(b.Sex,'保密') as Sex,
isnull(c.CityName,'火星') as City,(select count(UserID) from SNS_Attention
where AttentionUserID=a.AttentionUserID) as Fans,
e.TopicBody,e.TopicImg,e.createon
from SNS_Attention a
left join userinfo b on a.AttentionUserID=b.userid
left join City c on b.CityID=c.CityID
left join SNS_AttentionGroup d on a.AttentionGroupID=d.AttentionGroupID
inner join
(
select a.Userid , a.TopicBody,a.TopicImg,a.createon
from SNS_Topic a
inner join
( --分页begin
select top(@top) * from
(
select row_number() over(order by createon desc) rowNum,* from
(
select userid,max(createon) as Createon from sns_topic where userid in
(
select attentionuserid from sns_attention where userid=@userid
)
group by userid
) as temp
) as t
where t.rowNum>(@top*(@index-1))
--分页end
) as tempTopic
on a.UserID=tempTopic.userid
and a.createon =tempTopic.createon
where a.UnShield=0
) as e
on a.AttentionUserid=e.UserID
where a.userid=@userid
order by e.createon desc
end
select a.TopicBody,a.TopicImg,a.CreateON,b.UserID,b.nickname
from SNS_Topic a
inner join
(
select a.UserID,max(a.createon) as Createon,b.nickname,b.headimg from SNS_Topic a
inner join
(
select top 50 * from
(
select row_number() over(order by dbo.f_GetPy(t.nickname) desc) as Num, * from
(
select isnull(nickname,email) as nickname ,UserID,headImg from UserInfo
where UserID in
(
select attentionUserID from SNS_attention where UserID=444
)
) as t
) as temp
where num>0
) b
on a.UserID=b.UserID
where a.UnShield=0
group by a.UserID,b.Num,b.NickName,b.Headimg,b.UserID
) b
on a.UserID=b.UserID and a.CreateON=b.Createon
order by dbo.f_GetPy(b.nickname) desc
--1
select * from 文章表 a where not exists(select 1 from 文章表 where UserID=a.UserID and CreateON>a.CreateON)
--2
select * from 文章表 a where CreateON=(select max(CreateON) from 文章表 where UserID=a.UserID )