存档_know_list

DECLARE @ctgid VARCHAR(30)='01'
DECLARE @userid VARCHAR(30)
DECLARE @key1 VARCHAR(30)
DECLARE @pageindex INT
DECLARE @pagesize INT
DECLARE @qry INT
DECLARE @part VARCHAR(30)
DECLARE @cycle VARCHAR(30)
DECLARE @type VARCHAR(30)
DECLARE @order VARCHAR(30)
DECLARE @ctg NVARCHAR(30)


SET @ctgid='0301'--类别
SET @userid='201803010002'--用户id
SET @key1=''--模糊查询关键字
SET @part=''--部位
SET @cycle=''--周期
SET @type='01'--类型
SET @order='2'--排序
--排序
SET @pageindex=1--第几页
SET @pagesize=5--每页记录数
SET @qry=1--是否第一次查询
SET @ctg =''
--声明临时表

CREATE TABLE #knowinfo(
    [_id] VARCHAR(30),
    [d_title] NVARCHAR(200),
    [d_cid] VARCHAR(30),
    [d_cover] VARCHAR(150),
    [d_create] VARCHAR(30),
    [d_createid] VARCHAR(30),
    [d_depname] VARCHAR(150),
    [d_source] VARCHAR(30),
    [d_author] VARCHAR(30),
    [d_date] DATETIME,
    [d_part] VARCHAR(30),
    [d_cycle] VARCHAR(30),
    [d_photo] VARCHAR(550),
    [d_head] VARCHAR(550),
    [d_read] INT,
    [d_comment] INT,
    [d_collect] INT,
    [d_praise] INT,
    [isread] INT,
    [childtype] VARCHAR(30),
    [sourcetype] VARCHAR(30),
    [iscomment] INT,
    [iscollect] INT,
    [ispraise] INT,
    [type] VARCHAR(30),
    [d_top] INT
)



INSERT  INTO [#knowinfo] ([_id],[d_title],[d_cid],[d_cover], [d_create], [d_createid], [d_depname], [d_source], [d_author], [d_date], [d_part], [d_cycle], [d_photo], 
                          [d_head], [d_read], [d_comment], [d_collect], [d_praise], [isread], [childtype], [sourcetype], [iscomment], [iscollect], [ispraise], [type],[d_top])     
    SELECT 

    [tb1].[d_id] AS _id,
    [tb1].[d_title],--ing
    [tb1].[d_cid] ,
    [tb1].[d_cover] AS d_cover,
    [tb1].[d_create],
    [tb1].[d_createid] ,
    [tb3].[dep_name] AS d_depname,--ing
    [tb1].[d_source],
    [tb1].[d_author],
    [tb1].[d_date],

    [tb1].[d_part],
    [tb1].[d_cycle],
    (SELECT STUFF((
                  ----stuff函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符
                  SELECT TOP(9)',' + CAST([t].[da_filedesc] AS VARCHAR(150)) ---字段拼接
                    FROM [dbo].[busi_attach] [t]
                   WHERE 1   = 1
                     AND [t].[da_billid] = [a].[d_id]
                  FOR XML PATH('')),
              1,
              1,
              '') AS [name]
   --stuff将参数4的空字符串在参数1字符串的第1个（参数2）字符位置起替换掉1（参数3）个长度
   FROM [dbo].[know_details] [a] WHERE [a].[d_id]=[tb1].[d_id])
    AS d_photo,
    [tb2].[cu_photo] AS d_head,
    [tb1].[d_read],
    [tb1].[d_comment],
    [tb1].[d_collect],
    [tb1].[d_praise],
    (SELECT COUNT(*) FROM [dbo].[know_log] WHERE  [l_artid] = [tb1].[d_id] AND [l_readerid]=@userid AND [l_ctgid] LIKE [tb1].[d_cid] +'%') 
     AS isread,
    tb4.[c_name] AS childtype,
    tb4.[c_sourcetype] AS sourcetype,
    (SELECT COUNT(*) FROM [dbo].[busi_needreadmsg] WHERE [n_billid]=[tb1].[d_id] AND [n_source]='know' AND [n_createid]=@userid AND [n_ctg]='01')
     AS iscomment,
     (SELECT COUNT(*) FROM [dbo].[busi_needreadmsg] WHERE [n_billid]=[tb1].[d_id] AND [n_source]='know' AND [n_createid]=@userid AND [n_ctg]='08')
     AS iscollect,
    (SELECT COUNT(*) FROM [dbo].[busi_needreadmsg] WHERE [n_billid]=[tb1].[d_id] AND [n_source]='know' AND [n_createid]=@userid AND [n_ctg]='02')
    AS  ispraise,
    '' AS [type],
    [d_top]


FROM (SELECT*, ROW_NUMBER() OVER(ORDER BY [d_id] desc) as counts from [know_details] WHERE [d_cid]=@ctgid AND [d_title] LIKE '%'+@key1+'%' ) tb1 ---烟草农业科学

 LEFT JOIN(
 SELECT [cu_photo],[cu_depid],[cu_userid] FROM [dbo].[comp_sysuser]
 ) tb2 ON [tb1].[d_createid] =tb2.[cu_userid]

 LEFT JOIN(
 SELECT [dep_name],[dep_id] FROM [dbo].[comp_depart] 
 ) tb3 ON  [tb3].[dep_id] = [tb2].[cu_depid]

 LEFT JOIN(
 SELECT [c_name],[c_sourcetype],[c_id] FROM [dbo].[know_ctg]
 ) tb4 ON tb4.[c_id]=[tb1].[d_cid]


WHERE [tb1].[counts] between @pagesize*(@pageindex-1) and @pagesize*(@pageindex)-1 

--如果是专家
IF(@ctgid='0404')
BEGIN
INSERT INTO [#knowinfo] ([_id], [d_title], [d_cid], [d_cover], [d_create], [d_createid], [d_depname], [d_source], [d_author], [d_date], [d_part], [d_cycle], [d_photo],
                         [d_head], [d_read], [d_comment], [d_collect], [d_praise], [isread], [childtype], [sourcetype], [iscomment], [iscollect], [ispraise], [type],[d_top])

SELECT 
        [z_id] AS _id,
    [z_name] AS d_title,
'0404' AS d_cid,
[z_photo] AS d_cover,
[z_organ] AS d_create,
'' AS d_createid,
'' AS d_depname,
[z_direction] AS d_source,
[z_school] AS d_author,
'' AS d_date,
'' AS d_part,
''  AS d_cycle,
''  AS d_photo,
''  AS d_head,
0   AS d_read,
0   AS d_comment,
0   AS d_collect,
0   AS d_praise,
0   AS d_isread,
'专家库'   AS childtype,
'04'    AS sourcetype,
''  AS iscomment,
''  AS iscollect,
''  AS ispraise,
[z_ctg] AS [type],
'' AS d_top

FROM [dbo].[base_zy] WHERE 1=1 AND ( [z_name] LIKE @key1+'%' AND  [z_ctg] LIKE @type+'%')

 END




SELECT COUNT(*) AS [count] FROM [#knowinfo]  WHERE [d_part] LIKE '%'+@part+'%' and [d_cycle] LIKE  '%'+@cycle+'%'

SELECT * FROM  [#knowinfo]
WHERE
[d_part] LIKE '%'+@part+'%' and [d_cycle] LIKE  '%'+@cycle+'%'
ORDER BY 
    CASE WHEN @order='0' THEN [d_top] END ASC,
    CASE WHEN @order='-1' THEN [d_date] END ASC,
    CASE WHEN @order='1' THEN [d_date] END DESC,
    CASE WHEN @order='-2' THEN [d_read] END asc,
    CASE WHEN @order='2' THEN [d_read] END DESC,
    CASE WHEN @order='-3' THEN [d_comment] END ASC,
    CASE WHEN @order='3' THEN [d_comment] END DESC,
    CASE WHEN @order='-4' THEN [d_collect] END ASC,
    CASE WHEN @order='4' THEN [d_collect] END DESC,
    CASE WHEN @order='-5' THEN [d_praise] END ASC,
    CASE WHEN @order='5' THEN [d_praise] END DESC


DROP TABLE [#knowinfo]