一个基于分页的复杂SQL查询语句(一)-知识库

文章类型：	DATABASE
关键词：	复杂,SQL,查询,子查询,in,join,union
内容摘要：

一个基于分页的复杂SQL查询语句(一)

2015/7/29 11:34:41 来源：apple 阅读：

--这个复杂查询用到的相关sql脚本

/****** Object: StoredProcedure [dbo].[usp_GetArticlePagerData] Script Date: 04/28/2015 16:19:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_GetArticlePagerData]

-- Add the parameters for the stored procedure here

@strOrder nvarchar(255)='', -- 排序的字段名，必填

@strOrderType nvarchar(10)='ASC', -- 排序的方式,默认ASC

@pageSize int = 10, -- 页尺寸,默认

@pageIndex int = 1 -- 页码,默认

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- -- Insert statements for procedure here

declare @strSQL nvarchar(4000)=''

if(@strOrder = 'LastCommentTime')

begin

set @strSQL='select top '+CONVERT(varchar(3),@pageSize)+' * from '

+'(select * from (select top 99.999999 percent * from View_SelectArticles where CommentCount>0 order by LastCommentTime '+@strOrderType+') t1'

+' union all'

+' select * from (select top 99.999999 percent * from View_SelectArticles where CommentCount is NULL order by CreatedTime '+@strOrderType+') t2'

+') LastCommentTimeTable where ArticleId not in ('

+'select top '+CONVERT(varchar(10),(@pageIndex-1)*@pageSize)+' ArticleId from '

+'(select * from (select top 99.999999 percent * from View_SelectArticles where CommentCount>0 order by LastCommentTime '+@strOrderType+') t1'

+' union all'

+' select * from (select top 99.999999 percent * from View_SelectArticles where CommentCount is NULL order by CreatedTime '+@strOrderType+') t2'

+') LastCommentTimeTable)'

end

else

begin

if(@strOrder = 'CommentCount')

set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+',LastCommentTime '+@strOrderType+') AS RowNumber,* from View_SelectArticles)'

+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)

else if(@strOrder = 'Clicks')

set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+',CreatedTime '+@strOrderType+') AS RowNumber,* from View_SelectArticles)'

+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)

else

set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS RowNumber,* from View_SelectArticles)'

+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)

end

exec sp_executesql @strSQL

END

/****** Object: Table [dbo].[UserInfo] Script Date: 04/28/2015 16:19:38 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[UserInfo](

[UserId] [bigint] IDENTITY(1000,1) NOT NULL,

[LoginName] [nvarchar](30) NULL,

CONSTRAINT [PK_UsersInfo_1] PRIMARY KEY CLUSTERED

(

[UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[UserInfo] ON

INSERT [dbo].[UserInfo] ([UserId], [LoginName]) VALUES (100, N'stone')

INSERT [dbo].[UserInfo] ([UserId], [LoginName]) VALUES (1002, N' sadf1')

INSERT [dbo].[UserInfo] ([UserId], [LoginName]) VALUES (1010, N's123')

INSERT [dbo].[UserInfo] ([UserId], [LoginName]) VALUES (1006, N' dsfa3')

SET IDENTITY_INSERT [dbo].[UserInfo] OFF

/****** Object: Table [dbo].[Article] Script Date: 04/28/2015 16:19:38 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Article](

[ArticleId] [bigint] IDENTITY(1,1) NOT NULL,

[Title] [nvarchar](200) NOT NULL,

[ArticleKeywords] [nvarchar](200) NOT NULL,

[Summary] [nvarchar](500) NULL,

[IsHighLightCode] [bit] NOT NULL,

[ImgFileNameList] [nvarchar](1000) NULL,

[CreatedTime] [datetime] NOT NULL,

[ModifiedTime] [datetime] NULL,

[IsPublished] [bit] NOT NULL,

[IsEnabledComment] [bit] NOT NULL,

[Clicks] [int] NOT NULL,

[CategoryId] [int] NOT NULL,

[RelevantCategoryId] [nvarchar](50) NULL,

[UserId] [bigint] NOT NULL,

CONSTRAINT [PK_Article] PRIMARY KEY NONCLUSTERED

(

[ArticleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[Article] ON

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (63, N'dddddddddddddd1', N'333333333311111111', N'ew', 0, N'', CAST(0x0000A22B012BB62C AS DateTime), CAST(0x0000A22B012BB62C AS DateTime), 1, 0, 0, 9, NULL, 1010)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (62, N'dddddddddd', N'1111111111111', N'233333333', 0, N'', CAST(0x0000A22B0122A384 AS DateTime), CAST(0x0000A22B0122A384 AS DateTime), 1, 0, 2, 9, NULL, 1010)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (101, N'dsdfas', N'sa', N'as', 0, N'', CAST(0x0000A1EF0130AED4 AS DateTime), CAST(0x0000A24A01820964 AS DateTime), 1, 0, 2, 8, N'1,2,3,4,5,6,7,8,9', 100)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (52, N'111111111asdfasd23sdfas', N'sdaf,aaa,bb,asda', N'asdf', 0, N'', CAST(0x0000A10B011584EC AS DateTime), CAST(0x0000A24901340AFC AS DateTime), 1, 0, 445, 3, N'1,2,5,7', 100)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (8, N'财经文章标题测试', N'关键词,关键词,关键词', N'财经内容摘要，是淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的淡淡的', 0, N'', CAST(0x0000A0E3010A6175 AS DateTime), CAST(0x0000A0F800A743A7 AS DateTime), 1, 1, 15, 3, NULL, 100)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (20, N'sdfazzz', N'sdafzzz,健，，健', N'asdfzz', 0, N'', CAST(0x0000948300721540 AS DateTime), CAST (0x0000A0F1018543E6 AS DateTime), 1, 0, 3, 3, NULL, 1006)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (103, N'sssssssssssss111', N'ssssssssssss11112', N'asdfsd', 0, N'', CAST(0x000093FA00AB8754 AS DateTime), CAST(0x0000940500B22CE4 AS DateTime), 1, 0, 1000, 8, N'1,2,4,6,8', 100)

INSERT [dbo].[Article] ([ArticleId], [Title], [ArticleKeywords], [Summary], [IsHighLightCode], [ImgFileNameList], [CreatedTime], [ModifiedTime], [IsPublished], [IsEnabledComment], [Clicks], [CategoryId], [RelevantCategoryId], [UserId]) VALUES (64, N'dddddddddddd', N'sssss', N'dddddddd', 0, N'', CAST(0x000093E700E296A4 AS DateTime), CAST(0x0000A22C00E296A4 AS DateTime), 1, 0, 0, 9, NULL, 100)

SET IDENTITY_INSERT [dbo].[Article] OFF

/****** Object: Table [dbo].[ArticleComment] Script Date: 04/28/2015 16:19:38 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[ArticleComment](

[Id] [bigint] IDENTITY(1,1) NOT NULL,

[CommentDate] [datetime] NOT NULL,

[CommentContent] [ntext] NULL,

[HostIP] [nvarchar](50) NULL,

[ArticleId] [bigint] NOT NULL,

[UserId] [bigint] NOT NULL,

[ParentId] [bigint] NOT NULL,

CONSTRAINT [PK_ArticleComments] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[ArticleComment] ON

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (2, CAST(0x00009DC500C91AD0 AS DateTime), N'13121', NULL, 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (7, CAST(0x0000A11500CDD691 AS DateTime), N'1231231', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (8, CAST(0x0000A11500CE6E16 AS DateTime), N'asdfad', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (9, CAST(0x0000A11500CE78DA AS DateTime), N'asdfadasdfasd', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (10, CAST(0x0000A11500CED17E AS DateTime), N'asdfads', N'127.0.0.1', 62, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (11, CAST(0x0000A11500CEDA8F AS DateTime), N'asdfads1231231', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (12, CAST(0x0000A24201450890 AS DateTime), N'qqqqqqqqqqqqqqqq', N'::1', 62, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (13, CAST(0x0000A117014585D8 AS DateTime), N'2222222222222', N'::1', 52, 1002, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (15, CAST(0x0000A1170158A756 AS DateTime), N'3333333333333333333333', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (18, CAST(0x0000A136000B2424 AS DateTime), N'1453ddfad', N'::1', 20, 1002, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (19, CAST(0x0000A118000F091C AS DateTime), N'ddddddddddddddddd', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (20, CAST(0x0000A118000FCD19 AS DateTime), N'ffffffffffff', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (21, CAST(0x0000A118000FE7CA AS DateTime), N'hhhhhhhhhhhhhhhhhhhhhh', N'::1', 103, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (22, CAST(0x0000A11800107AE4 AS DateTime), N'hhhhhhhhhhhhhhhhhhhhhh6666666666666', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (23, CAST(0x0000A11800111DFC AS DateTime), N'ttttttttttttttttttt', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (24, CAST(0x0000A11800112D93 AS DateTime), N'gggggggggggggggggg', N'::1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (25, CAST(0x000094610002E104 AS DateTime), N'不错哦', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (27, CAST(0x00009461003B48BB AS DateTime), N'asdfasd', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (28, CAST(0x00009461003C9DD2 AS DateTime), N'ewwwwwwwwwwwwwwwwwwww', N'127.0.0.1', 52, 100, 29)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (29, CAST(0x0000A118011ED398 AS DateTime), N'333333333333333333', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (30, CAST(0x0000A16B00B5B442 AS DateTime), N'dddddddddddddddddddd', N'127.0.0.1', 103, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (97, CAST(0x000094620147A9DC AS DateTime), N'请输入您的留言信息sdfa...', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (98, CAST(0x000094620147BC39 AS DateTime), N'111111111请输入您的留言信息sdfa...', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (99, CAST(0x000094620147E494 AS DateTime), N'111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...111111111请输入您的留言信息sdfa...', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (100, CAST(0x0000951800C317D4 AS DateTime), N'请输入您的留言信息...我的子评论', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (101, CAST(0x0000951800C363BE AS DateTime), N'请输入您的留言信息...我的评论', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (102, CAST(0x0000951800C63192 AS DateTime), N'请输入您的留言信息...我的评论', N'127.0.0.1', 52, 100, 0)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (103, CAST(0x0000A29300EAC61D AS DateTime), N'请输入您的留言信息...aaaaaaaaaaaaa', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (104, CAST(0x0000A29300EB371A AS DateTime), N'请输入您的留言信息...aaaaaaaaaaaaa1111111111', N'127.0.0.1', 52, 100, 103)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (105, CAST(0x0000951C00D30D84 AS DateTime), N'请输入您的留言子评论信息...', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (106, CAST(0x0000951C00EEECD8 AS DateTime), N'请输入您的留子评论言信息...', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (107, CAST(0x0000951C00EF15C4 AS DateTime), N'请输入您的留子评论言信息...', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (108, CAST(0x0000951C00EF1BD3 AS DateTime), N'请输入您的留子评论言信息...', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (109, CAST(0x0000951C00F5B0F7 AS DateTime), N'请输入您的ddddddd子评论留言信息...', N'127.0.0.1', 52, 100, 98)

INSERT [dbo].[ArticleComment] ([Id], [CommentDate], [CommentContent], [HostIP], [ArticleId], [UserId], [ParentId]) VALUES (110, CAST(0x0000A2970120B07A AS DateTime), N'请输入您的留言子评论信息...', N'127.0.0.1', 52, 100, 98)

SET IDENTITY_INSERT [dbo].[ArticleComment] OFF

/****** Object: View [dbo].[View_SelectArticles] Script Date: 04/28/2015 16:19:39 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create view [dbo].[View_SelectArticles]

select B.ArticleId,Title,AuthorId,LoginName Author,CreatedTime,ModifiedTime,Clicks,CommentCount,LastCommentTime,Commentator from

(

select CommentCountTable.ArticleId,Article.Title,Article.UserId AuthorId,Article.CreatedTime,Article.ModifiedTime, CommentCount,Article.Clicks,Commentator,LastCommentTime from

(select ArticleId, CommentCount,LoginName Commentator,LastCommentTime from

(select a.ArticleId,a.CommentCount,a.LastCommentTime,UserId CommentatorId from (select ArticleId,COUNT(ArticleId) CommentCount,MAX(CommentDate) LastCommentTime from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and a.LastCommentTime=ArticleComment.CommentDate

)

as CommentTableA

join UserInfo on CommentTableA.CommentatorId=UserInfo.UserId

union

select ArticleId, CommentCount=NULL,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(select ArticleId from ArticleComment)

) as CommentCountTable

join Article on Article.ArticleId=CommentCountTable.ArticleId

) B join UserInfo on b.AuthorId=UserInfo.UserId

/****** Object: Default [DF_ArticleComment_CommentDate] Script Date: 04/28/2015 16:19:38 ******/

ALTER TABLE [dbo].[ArticleComment] ADD CONSTRAINT [DF_ArticleComment_CommentDate] DEFAULT (getdate()) FOR [CommentDate]

/****** Object: Default [DF_ArticleComment_ParentId] Script Date: 04/28/2015 16:19:38 ******/

ALTER TABLE [dbo].[ArticleComment] ADD CONSTRAINT [DF_ArticleComment_ParentId] DEFAULT ((0)) FOR [ParentId]

/****** Object: ForeignKey [FK_Article_UserInfo] Script Date: 04/28/2015 16:19:38 ******/

ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_Article_UserInfo] FOREIGN KEY([UserId])

REFERENCES [dbo].[UserInfo] ([UserId])

ALTER TABLE [dbo].[Article] CHECK CONSTRAINT [FK_Article_UserInfo]

/****** Object: ForeignKey [FK_ArticleComment_Article] Script Date: 04/28/2015 16:19:38 ******/

ALTER TABLE [dbo].[ArticleComment] WITH CHECK ADD CONSTRAINT [FK_ArticleComment_Article] FOREIGN KEY([ArticleId])

REFERENCES [dbo].[Article] ([ArticleId])

ALTER TABLE [dbo].[ArticleComment] CHECK CONSTRAINT [FK_ArticleComment_Article]

/****** Object: ForeignKey [FK_ArticleComments_UsersInfo] Script Date: 04/28/2015 16:19:38 ******/

ALTER TABLE [dbo].[ArticleComment] WITH CHECK ADD CONSTRAINT [FK_ArticleComments_UsersInfo] FOREIGN KEY([UserId])

REFERENCES [dbo].[UserInfo] ([UserId])

ALTER TABLE [dbo].[ArticleComment] CHECK CONSTRAINT [FK_ArticleComments_UsersInfo]

这个查询从3个表中查询结果，需要查询的数据包括：文章作者ID，作者，文章标题，文章创建时间，更新时间，点击量，评论回复数量，最近回复时间，回复人。其中“评论回复数量”和“最近回复日期”需要从评论表中查询统计，而“最近回复人”通过评论表查询得到；“作者”和“回复人”来自于用户信息表；其他的则来自于文章表中。

下面我们来分步解析如何查询得到这些数据信息：

1、先从评论表中得到参与评论的文章信息（包括：文章ID、评论数、最近评论时间）。Sql查询语句如下

select ArticleId,COUNT(ArticleId) CommentCount, MAX(CommentDate) LastCommentTime from ArticleComment group by ArticleId

执行效果如下所示：

ArticleId CommentCount LastCommentTime

-------------------- ------------ ------------------------------------------------

20 1 2012-12-29 00:40:33.827

52 30 2013-12-17 17:31:05.153

62 2 2013-09-23 19:43:24.640

103 2 2013-02-20 11:01:33.873

2、然后再联接评论表查询查询最后参与文章评论的评论者的ID。Sql查询语句如下

Select A.ArticleId,CommentCount,UserId,LastCommentTime from (select ArticleId,COUNT(ArticleId) CommentCount, MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate

执行效果如下所示：

ArticleId CommentCount UserId LastCommentTime

-------------------- ------------ -------------------- ------------------------------------------------------------

20 1 1002 2012-12-29 00:40:33.827

52 30 100 2013-12-17 17:31:05.153

62 2 100 2013-09-23 19:43:24.640

103 2 100 2013-02-20 11:01:33.873

3、接着再联接用户信息表查询出参与文章评论的评论者的账户名称。Sql查询语句如下

select ArticleId,CommentCount,LoginName Commentator,LastCommentTime from (select A.ArticleId,CommentCount,UserId,LastCommentTime from (select ArticleId,COUNT(ArticleId) CommentCount, MAX(CommentDate) LastCommentTime from ArticleComment group by ArticleId) A join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate) B join UserInfo on(B.UserId=UserInfo.UserId)

执行效果如下所示：

ArticleId CommentCount Commentator LastCommentTime

-------------------- ------------ ------------------------------ -----------------------

20 1 sadf1 2012-12-29 00:40:33.827

52 30 stone 2013-12-17 17:31:05.153

62 2 stone 2013-09-23 19:43:24.640

103 2 stone 2013-02-20 11:01:33.873

4、同样道理，再根据文章信息表查询出没有参与评论的文章信息。Sql查询语句如下

select ArticleId,CommentCount=0,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(select A.ArticleId from (select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime from ArticleComment group by ArticleId) A join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate)

执行效果如下所示：

ArticleId CommentCount Commentator LastCommentTime

-------------------- ------------ ----------- ---------------

8 0 NULL NULL

61 0 NULL NULL

63 0 NULL NULL

64 0 NULL NULL

65 0 NULL NULL

67 0 NULL NULL

68 0 NULL NULL

101 0 NULL NULL

5、然后，合并参加评论和没有参加评论的查询结果信息。Sql查询语句如下

select ArticleId,CommentCount=0,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(

select A.ArticleId from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate)

union

select ArticleId,CommentCount,LoginName Commentator,LastCommentTime

from

(select A.ArticleId,CommentCount,UserId,LastCommentTime from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate

) B

join UserInfo on(B.UserId=UserInfo.UserId)

执行效果如下所示：

ArticleId CommentCount Commentator LastCommentTime

-------------------- ------------ ------------------------------ -----------------------

8 0 NULL NULL

61 0 NULL NULL

63 0 NULL NULL

64 0 NULL NULL

65 0 NULL NULL

67 0 NULL NULL

68 0 NULL NULL

101 0 NULL NULL

20 1 sadf1 2012-12-29 00:40:33.827

52 30 stone 2013-12-17 17:31:05.153

62 2 stone 2013-09-23 19:43:24.640

103 2 stone 2013-02-20 11:01:33.873

6、然后，将上面的查询结果信息通过与文章信息表进行联合查询，增加查询的列，包括：文章的标题、文章的作者ID、文章的创建时间、更新时间、点击量。Sql查询语句如下

select C.ArticleId,Title,CreatedTime,ModifiedTime,UserId,Clicks,CommentCount,Commentator,LastCommentTime

from

(select ArticleId,CommentCount=0,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(

select A.ArticleId from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate)

union

select ArticleId,CommentCount,LoginName Commentator,LastCommentTime

from

(select A.ArticleId,CommentCount,UserId,LastCommentTime from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate

) B

join UserInfo on(B.UserId=UserInfo.UserId)

) C

join Article on(c.ArticleId=Article.ArticleId)

执行效果如下所示：

ArticleId Title CreatedTime ModifiedTime UserId Clicks CommentCount Commentator LastCommentTime

-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- -------------------- ----------- ------------ ------------------------------ -----------------------

8 财经文章标题测试 2012-10-07 16:09:51.750 2012-10-28 10:08:59.437 100 15 0 NULL NULL

61 sad 2013-08-31 17:35:15.000 2013-08-31 17:35:15.000 1010 0 0 NULL NULL

63 dddddddddddddd1 2013-08-31 18:11:13.000 2013-08-31 18:11:13.000 1010 0 0 NULL NULL

64 dddddddddddd 2003-09-01 13:44:59.000 2013-09-01 13:44:59.000 100 0 0 NULL NULL

65 drrr 2013-09-01 14:29:09.000 2013-09-01 14:29:09.000 100 15 0 NULL NULL

67 ddddd 2013-09-01 14:38:40.000 2013-09-01 14:38:40.000 100 0 0 NULL NULL

68 dddddd 2013-09-01 14:44:34.000 2013-09-01 14:44:34.000 100 0 0 NULL NULL

101 dsdfas 2013-07-02 18:29:19.000 2013-10-01 23:25:31.000 100 2 0 NULL NULL

20 sdfazzz 2004-02-04 06:55:21.813 2012-10-21 23:37:16.287 1006 3 1 sadf1 2012-12-29 00:40:33.827

52 111111111asdfasd23sdfas 2012-11-16 16:50:25.000 2013-09-30 18:41:33.000 100 445 30 stone 2013-12-17 17:31:05.153

62 dddddddddd 2013-08-31 17:38:11.000 2013-08-31 17:38:11.000 1010 2 2 stone 2013-09-23 19:43:24.640

103 sssssssssssss111 2003-09-20 10:24:31.000 2003-10-01 10:48:43.000 100 1000 2 stone 2013-02-20 11:01:33.873

7、最后，将上面的查询结果信息通过与用户信息表进行联合查询，增加查询的列，包括：作者的账户名称。Sql查询语句如下

select ArticleId,Title,CreatedTime,ModifiedTime,LoginName Author,Clicks,CommentCount,Commentator,LastCommentTime

from

(select C.ArticleId,Title,CreatedTime,ModifiedTime,UserId,Clicks,CommentCount,Commentator,LastCommentTime

from

(select ArticleId,CommentCount=0,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(

select A.ArticleId from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate)

union

select ArticleId,CommentCount,LoginName Commentator,LastCommentTime

from

(select A.ArticleId,CommentCount,UserId,LastCommentTime from

(select ArticleId,COUNT(ArticleId) CommentCount,

MAX(CommentDate) LastCommentTime

from ArticleComment group by ArticleId) A

join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate

) B

join UserInfo on(B.UserId=UserInfo.UserId)

) C

join Article on(c.ArticleId=Article.ArticleId)

) D

join UserInfo on(D.UserId=UserInfo.UserId)

执行效果如下所示：

ArticleId Title CreatedTime ModifiedTime Author Clicks CommentCount Commentator LastCommentTime

-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ------------------------------ ----------- ------------ ------------------------------ -----------------------

8 财经文章标题测试 2012-10-07 16:09:51.750 2012-10-28 10:08:59.437 stone 15 0 NULL NULL

61 sad 2013-08-31 17:35:15.000 2013-08-31 17:35:15.000 s123 0 0 NULL NULL

63 dddddddddddddd1 2013-08-31 18:11:13.000 2013-08-31 18:11:13.000 s123 0 0 NULL NULL

64 dddddddddddd 2003-09-01 13:44:59.000 2013-09-01 13:44:59.000 stone 0 0 NULL NULL

65 drrr 2013-09-01 14:29:09.000 2013-09-01 14:29:09.000 stone 15 0 NULL NULL

67 ddddd 2013-09-01 14:38:40.000 2013-09-01 14:38:40.000 stone 0 0 NULL NULL

68 dddddd 2013-09-01 14:44:34.000 2013-09-01 14:44:34.000 stone 0 0 NULL NULL

101 dsdfas 2013-07-02 18:29:19.000 2013-10-01 23:25:31.000 stone 2 0 NULL NULL

20 sdfazzz 2004-02-04 06:55:21.813 2012-10-21 23:37:16.287 dsfa3 3 1 sadf1 2012-12-29 00:40:33.827

52 111111111asdfasd23sdfas 2012-11-16 16:50:25.000 2013-09-30 18:41:33.000 stone 445 30 stone 2013-12-17 17:31:05.153

62 dddddddddd 2013-08-31 17:38:11.000 2013-08-31 17:38:11.000 s123 2 2 stone 2013-09-23 19:43:24.640

103 sssssssssssss111 2003-09-20 10:24:31.000 2003-10-01 10:48:43.000 stone 1000 2 stone 2013-02-20 11:01:33.873

待续……

↑ 上一篇文章：SQL逻辑编程关键词：SQL,逻辑,编程发布日期：2015/7/29 11:33:06
↓ 下一篇文章：一个基于分页的复杂SQL查询语句(续) 关键词：复杂,SQL,查询,子查询,in,join,union 发布日期：2015/7/29 11:36:05

相关文章：
一个基于分页的复杂SQL查询语句(续) 关键词：复杂,SQL,查询,子查询,in,join,union 发布日期：2015-07-29 11:36
数据库语句之内连接和外连接及联合关键词：SQL,sql,Server,数据库,语句,大全,联合语句,UNION,内连接查询,外连接查询,.. 发布日期：2015-07-29 14:29
使用C#编程和SQL的存储过程来处理日期时间的复合查询问题关键词：C#,SQL,存储过程,日期时间,时间日期,DateTime,复杂查询发布日期：2015-07-14 16:20

相关目录：.NET，DATABASE，软件开发，ANDROID