一个存储过程

USE [MainDb]
GO
/****** Object:  StoredProcedure [dbo].[pcPaChatOnLineUserLogSelect]    Script Date: 06/20/2014 13:56:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--获取求职者的15日内联系人列表，或者有留言的企业,按照未读消息个数以及联系时间倒序排列
ALTER PROCEDURE [dbo].[pcPaChatOnLineLogSelect]
    @PaMainID INT--求职者的ID
AS
BEGIN
    
    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 desc
END

 

转载于:https://www.cnblogs.com/ustcyc/p/3799229.html