USE [MainDb]GO/****** Object:  StoredProcedure [dbo].[pcPaChatOnLineUserLogSelect]    Script Date: 06/20/2014 13:56:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--获取求职者的15日内联系人列表，或者有留言的企业,按照未读消息个数以及联系时间倒序排列ALTER PROCEDURE [dbo].[pcPaChatOnLineLogSelect]    @PaMainID INT--求职者的IDASBEGIN        if exists (select * from tempdb.dbo.sysobjects where id = object_id(N‘tempdb..ChatLog‘) and type=‘U‘)        drop table ChatLog    create table ChatLog    (        ChatID int,        CpName Nvarchar(20),        LastMsg smallDatetime,        LastDate smallDatetime    )     --查出企业联系个人    insert into ChatLog            select ID, FromID as UserID, Adddate, sum(case isviewed when ‘0‘ then 1 when ‘1‘ then 0 end) as UnReadCount from         (           select Chat.ID, Chat.ChatType, FromID, ToID, Sender, Chat.AddDate, IsViewed           from MainDB..ChatOnline as Chat, MainDB..ChatOnlineLog as ChatLog           where Chat.ID = ChatLog.ChatOnlineID and ChatType=2 and ToID=@PaMainID and            --表示自己发送的，或者对方发送的自己没有查看的           ((sender=1) or (sender=0 and isviewed=‘0‘))        )T        group by ID, FromID, AddDate        --select * from ChatLog    --查出个人联系企业的    insert into ChatLog            select ID, ToID as UserID, Adddate, sum(case isviewed when ‘0‘ then 1 when ‘1‘ then 0 end) as UnReadCount from         (           select Chat.ID, Chat.ChatType, FromID, ToID, Sender, Chat.AddDate, IsViewed           from MainDB..ChatOnline as Chat, MainDB..ChatOnlineLog as ChatLog           where Chat.ID = ChatLog.ChatOnlineID and ChatType=1 and FromID=@PaMainID and            --表示自己发送的，或者对方发送的自己没有查看的           ((sender=0 and isViewed=‘0‘ ) or (sender=1))        )T        group by ID, ToID, AddDate        --select * from ChatLog    --结果,并获取企业的ID,hr的名字,企业的名字    select T.ID as ChatOnlineID, T.UserID as ChatUserID, Cp.ID as CpMainID, Cp.Name as CpName, Ca.ID as CaMainID,             Ca.Name as UserName, Cp.SecondID as SecondID, T.AddDate, T.SumUnRead,                       (case when (datediff("s", getdate(), O.refreshdate)) > 120 then 1 else 0 end) as IsOnline,--在线与否           (case when (GetDate()-T.AddDate>15 and T.UnReadCount = 0) then 1 else 0 end) as IsHinden--是否显示           from     (        --分组，排序，求和        select ID, UserID, AddDate, UnReadCount,                RowIndex=Row_number() over (partition by userID order by AddDate desc),                SumUnRead = sum(UnReadCount) over (partition by userID) from ChatLog         --where ((GetDate()-AddDate<=15 and UnReadCount = 0) or (UnReadCount>0))    ) T     left join MainDB..Job Job on Job.ID = T.UserID--对于企业来说，聊天的ID是JobID    left join MainDB..CaMain Ca on Job.CaMainID = Ca.ID    left join MainDB..CpMain Cp on Ca.CpMainID = Cp.ID    left join MainDB..CpOnline O on Ca.ID = O.CaMainID    where T.RowIndex = 1 Order by T.SumUnRead descEND