FYJ.Blogs开发系列(一)

FYJ.Blogs开发系列(二)-后台文章列表页View

FYJ.Blogs开发系列(三)-后台文章编辑页View

FYJ.Blogs开发系列(四)-后台文章Controller

FYJ.Blogs开发系列(五)-前台基页Controller

FYJ.Blogs开发系列(六)-前台主页Controller

FYJ.Blogs开发系列(七)-前台文章Controller

FYJ.Blogs开发系列(八)-自定义路由

FYJ.Blogs开发系列(九)-查询文章函数

FYJ.Blogs开发系列(十)-主页查询存储过程

FYJ.Blogs开发系列(十一)-后记

USE [db_blogs]
GO
/****** Object:  UserDefinedFunction [dbo].[blog_func_articleTable]    Script Date: 2014/5/29 23:42:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[blog_func_articleTable]
(	
	-- Add the parameters for the function here
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT table2.*
	,themeName
	,themeDisplay
	,linkScript
	 FROM (
		SELECT  
		       blog_tb_article.[articleID]
      ,blog_tb_article.[blogID]
      ,blog_tb_article.[siteCategoryID]
      ,blog_tb_article.[categoryID]
      ,blog_tb_article.[topicID]
      ,[articleTitle]
      ,[articleTitleColor]
      ,[articleKeywords]
      ,[articleDescription]
      ,[articleSource]
      ,[articleAuthor]
      ,[articleSourceUrl]
      ,[articlePic]
      ,[articleThumbPic]
      ,[articleDatetime]
      ,[articleRedirectUrl]
      ,[articleAddUserID]
      ,[articleModifyUserID]
      ,[articleOrder]
      ,[articleReplyCount]
      ,[articleIsTop]
      ,[articleIsDisabled]
      ,[articleCommentLimit]
      ,[articleIsIndex]
      ,[articleIsOriginal]
      ,[articleIsSystem]
      ,[articleIsPic]
      ,[articleIsDelete]
      ,[articleAttachmentLimit]
      ,[articlePassword]
      ,[articlePostBy]
      ,blog_tb_article.[ADD_DATE]
      ,blog_tb_article.[UPDATE_DATE],
			   ISNULL(blog_tb_article_extend.articleClickTimes,0) as articleClickTimes,
			   ISNULL(blog_tb_article_extend.articleCommentTimes,0) as articleCommentTimes,
		       dbo.blog_tb_blog.userID,
			   dbo.blog_tb_blog.blogName,
			   dbo.blog_tb_blog.blogTitle, 
               dbo.blog_tb_blog.blogDomain,
			  
               dbo.blog_func_articleTitle(blog_tb_article.articleTitle, blog_tb_article.articleTitleColor,blog_tb_article.articleIsTop,blog_tb_article.articleIsOriginal) AS articleTitle2, 
			   dbo.blog_func_categoryUrl(dbo.blog_tb_blog.blogName, dbo.blog_tb_blog.blogDomain,dbo.blog_tb_category.categoryID, dbo.blog_tb_category.categoryDomain) AS categoryUrl,
			  
			   dbo.blog_tb_category.categoryName, 
               dbo.blog_tb_category.categoryDisplay,
			   dbo.blog_tb_category.categoryDomain,
			   dbo.blog_func_articleThreadUrl(dbo.blog_tb_blog.blogName, dbo.blog_tb_blog.blogDomain, blog_tb_article.articleID, dbo.blog_tb_category.categoryDomain) AS articleUrl, 
			   dbo.blog_tb_topic.topicDisplay,
			   blog_tb_article_content.articleContent,
			   blog_tb_article_content.articleHideContent,
			   blog_tb_article_content.articleSubContentHtml,
			   blog_tb_article_content.articleSubContentText,
			   --dbo.blog_func_articThumbPic(blog_tb_article.articID,articThumbPic) as articThumbPic2,
               table1.tagUrls,
			   table1.tagDisplays,
			   blog_tb_theme.themeDisplay as articleThemeDisplay,
			    case when(blog_tb_article.articleIsDisabled=0)
			   then
			        case when(blog_tb_category.categoryIsDisabled=0)
					then
				    blog_tb_blog.blogIsDisabled
					else
					blog_tb_category.categoryIsDisabled
					end
			   else
			   blog_tb_article.articleIsDisabled
			   end 
			   as isDisabled,

			    case when(blog_tb_article.themeID is null or blog_tb_article.themeID='')
			   then
						case when(blog_tb_category.themeID is null or blog_tb_category.themeID='')
						then
							 case when(site_tb_category.themeID is null or site_tb_category.themeID='')
							 then
							   blog_tb_theme4.themeID
							 else
							 blog_tb_theme3.themeID
							 end
						else
						blog_tb_theme2.themeID
						end
				else
				blog_tb_article.themeID
				end 
				as themeID,

			   site_tb_category.categoryDisplay as siteCategoryDisplay
FROM      
(SELECT   * FROM      blog_tb_article ) AS blog_tb_article
 INNER JOIN dbo.blog_tb_blog ON blog_tb_article.blogID = dbo.blog_tb_blog.blogID 
 LEFT OUTER JOIN dbo.blog_tb_category ON blog_tb_article.categoryID = dbo.blog_tb_category.categoryID 
 LEFT OUTER JOIN dbo.blog_tb_topic ON blog_tb_article.topicID = dbo.blog_tb_topic.topicID
 LEFT JOIN blog_tb_article_content ON blog_tb_article_content.articleID=blog_tb_article.articleID
 LEFT JOIN blog_tb_article_extend ON blog_tb_article_extend.articleID=blog_tb_article.articleID
 LEFT JOIN site_tb_category on site_tb_category.categoryID=blog_tb_article.siteCategoryID
 LEFT JOIN blog_tb_theme on blog_tb_article.themeID=blog_tb_theme.themeID
 LEFT JOIN blog_tb_theme blog_tb_theme2 on blog_tb_category.themeID=blog_tb_theme2.themeID
 LEFT JOIN blog_tb_theme blog_tb_theme3 on site_tb_category.themeID=blog_tb_theme3.themeID
 LEFT JOIN blog_tb_theme blog_tb_theme4 on blog_tb_blog.themeID=blog_tb_theme4.themeID
 outer  apply dbo.blog_func_articleTag(blog_tb_article.articleID)  as table1 
 )
-- cross apply    ??
table2
LEFT JOIN blog_tb_theme on blog_tb_theme.themeID=table2.themeID
)