22,209
社区成员
发帖
与我相关
我的任务
分享
/****** Object: StoredProcedure [dbo].[Query_SerchResultList] Script Date: 03/31/2012 15:22:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Query_SerchResultList](
@type INT, --1微博/2用户/3微群
@serchtag VARCHAR(20), --搜索关键词
@pageindex INT, --页码
@pagesize INT, --数据大小
@isCount INT --是否需要得到行数
)
AS
DECLARE @sqlstr NVARCHAR(4000)
DECLARE @taghave INT
IF @isCount = 0
BEGIN
--搜索微博
IF @type = 1
BEGIN
SET @sqlstr =
'SELECT *
FROM (
SELECT mb.ApplicationID,
mb.BlogContent,
mb.BlogUntreatedContent,
mb.BlogSender,
mb.BlogForward,
mb.BlogFrom,
mb.IsFirstPub,
mb.BlogTime,
mb.IsDeleted,
mb.CommentCount,
mb.ForwardCount,
mb.OriginalBlogID,
gi.GName,
gi.GId,
ROW_NUMBER() OVER(ORDER BY BlogTime DESC) AS Num
FROM MicroBlog mb
LEFT OUTER JOIN GroupInfo gi
ON gi.GId = mb.GroupID
WHERE mb.IsDeleted = 0
AND mb.BlogContent LIKE ''' + '%' + @serchtag +
'%' +
'''
)t1
WHERE t1.Num BETWEEN (' + CAST(@PageSize AS VARCHAR) + ' * (' +
CAST(@PageIndex AS VARCHAR) + ' -1)) AND (' + CAST(@PageSize AS VARCHAR)
+ ' * ' + CAST(@PageIndex AS VARCHAR) +
')
ORDER BY t1.BlogTime DESC';
END
--搜索用户
IF @type = 2
BEGIN
SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) +
'
a.UserID,a.NickName,a.MyDiscription,a.SelfPicture,a.ConcernedCount,a.ConcerningCount,b.validated
FROM UserInfo a, SysUser b
Where a.NickName LIKE + ''' + '%' + @serchtag + '%' +
'''
AND a.UserID = b.UserID
AND a.UserID
NOT IN
(
SELECT TOP (' + CAST(@pagesize AS VARCHAR) + ' * ('
+
CAST(@pageindex AS VARCHAR) +
' - 1)) a.UserID
FROM UserInfo a, SysUser b
Where a.NickName LIKE + ''' + '%' + @serchtag + '%'
+
'''
AND a.UserID = b.UserID
ORDER BY BlogCount desc
)
ORDER BY BlogCount desc';
END
--搜索微群
IF @type = 3
BEGIN
SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) +
'
a.GClassName,b.GId,b.GImage,b.GName,b.GSpeakSize,b.GUserSize,b.GDescription,b.GCreateDate,c.NickName,c.UserID
FROM GroupClass a,GroupInfo b,UserInfo c
WHERE a.GClassId = b.GGroupId
AND b.GUserid = c.UserID
AND b.GPrivate = 1
AND b.GName LIKE + ''' + '%' + @serchtag + '%' +
'''
AND b.GId
NOT IN
(
SELECT TOP (' + CAST(@pagesize AS VARCHAR) + ' * ('
+
CAST(@pageindex AS VARCHAR) +
' - 1)) b.GId
FROM GroupClass a,GroupInfo b,UserInfo c
WHERE a.GClassId = b.GGroupId
AND b.GUserid = c.UserID
AND b.GPrivate = 1
AND b.GName LIKE + ''' + '%' + @serchtag + '%' +
'''
ORDER BY b.GScore desc
)
ORDER BY b.GScore desc';
END
EXEC (@sqlstr)
IF @serchtag <> ''
BEGIN
--是否已经存在此次搜索的keyword
SELECT @taghave = COUNT(ID)
FROM SerchRecord
WHERE Keyword = CAST(@serchtag AS VARCHAR)
--如果存在则做更新操作
IF @taghave > 0
BEGIN
UPDATE SerchRecord
SET SerchTime = GETDATE(),
SerchSize = (SerchSize + 1)
WHERE Keyword = @serchtag
END--如果不存在则做新增的操作
ELSE
BEGIN
INSERT INTO SerchRecord
(
ID,
Keyword,
SerchTime,
SerchSize
)
VALUES
(
NEWID(),
@serchtag,
GETDATE(),
1
)
END
END
END
IF @isCount = 1
BEGIN
--搜索微博
IF @type = 1
BEGIN
SET @sqlstr =
'SELECT COUNT(*)
FROM MicroBlog mb
WHERE mb.IsDeleted = 0 and mb.BlogContent LIKE ''' + '%' + @serchtag
+ '%' + '''';
END
--搜索用户
IF @type = 2
BEGIN
SET @sqlstr =
'
SELECT COUNT(a.UserID)
FROM UserInfo a,
SysUser b
WHERE a.NickName LIKE + ''' + '%' + @serchtag +
'%
' +
'''
AND a.UserID = b.UserID
';
END
--搜索微群
IF @type = 3
BEGIN
SET @sqlstr =
'
SELECT COUNT(b.GId)
FROM GroupClass a,
GroupInfo b,
UserInfo c
WHERE a.GClassId = b.GGroupId
AND b.GUserid = c.UserID
AND b.GPrivate = 1
AND b.GName LIKE + ''' + '%' + @serchtag +
'%
' + '''';
END
EXEC (@sqlstr)
END