这个存储过程的需求是这样的:
1、取出三张表里的符合条件的数据,合成一张表,并对这一张表分页;
2、计算合成的这张表总共多少条数据我写出来了,效果也达到了,但是总觉得太罗嗦了,请大侠看下能不能简化,我觉得有多数语句写了两边。ALTER PROCEDURE [dbo].[GetAnnouncementList]
@pageSize int,
@pageIndex int
AS
BEGIN
SET NOCOUNT ON;
declare @rowCount int
select @rowCount=COUNT(t2.newsID)from
(select * from
(select News.NewsID,News.NewsTitle,News.CreatedOn from dbo.News where CategoryID ='8b041bf4-24bf-4b35-85c7-f72717dc5752'
union all
select Activities.ActivityID,Activities.Subject,Activities.CreatedOn from dbo.Activities where Activities.CheckStatus=1 and IsDeleted=0 and Activities.RealEndTime>(select GETDATE())
union all
select Train.TrainID,Train.Subject,Train.CreatedOn from dbo.Train where CheckStatus=1 and Train.RealEndTime>(select GETDATE()))as t)as t2
select * from
(select ROW_NUMBER()over (order by temptable.CreatedOn desc)as tempNum, * from
(select News.NewsID,News.NewsTitle,News.CreatedOn from dbo.News where CategoryID ='8b041bf4-24bf-4b35-85c7-f72717dc5752'
union all
select Activities.ActivityID,Activities.Subject,Activities.CreatedOn from dbo.Activities where Activities.CheckStatus=1 and IsDeleted=0 and Activities.RealEndTime>(select GETDATE())
union all
select Train.TrainID,Train.Subject,Train.CreatedOn from dbo.Train where CheckStatus=1 and Train.RealEndTime>(select GETDATE()))as temptable)as temptable2
where temptable2.tempNum>(@pageIndex-1)*@pageSize and temptable2.tempNum<=(@pageIndex*@pageSize)
return @rowCount
END存储过程
1、取出三张表里的符合条件的数据,合成一张表,并对这一张表分页;
2、计算合成的这张表总共多少条数据我写出来了,效果也达到了,但是总觉得太罗嗦了,请大侠看下能不能简化,我觉得有多数语句写了两边。ALTER PROCEDURE [dbo].[GetAnnouncementList]
@pageSize int,
@pageIndex int
AS
BEGIN
SET NOCOUNT ON;
declare @rowCount int
select @rowCount=COUNT(t2.newsID)from
(select * from
(select News.NewsID,News.NewsTitle,News.CreatedOn from dbo.News where CategoryID ='8b041bf4-24bf-4b35-85c7-f72717dc5752'
union all
select Activities.ActivityID,Activities.Subject,Activities.CreatedOn from dbo.Activities where Activities.CheckStatus=1 and IsDeleted=0 and Activities.RealEndTime>(select GETDATE())
union all
select Train.TrainID,Train.Subject,Train.CreatedOn from dbo.Train where CheckStatus=1 and Train.RealEndTime>(select GETDATE()))as t)as t2
select * from
(select ROW_NUMBER()over (order by temptable.CreatedOn desc)as tempNum, * from
(select News.NewsID,News.NewsTitle,News.CreatedOn from dbo.News where CategoryID ='8b041bf4-24bf-4b35-85c7-f72717dc5752'
union all
select Activities.ActivityID,Activities.Subject,Activities.CreatedOn from dbo.Activities where Activities.CheckStatus=1 and IsDeleted=0 and Activities.RealEndTime>(select GETDATE())
union all
select Train.TrainID,Train.Subject,Train.CreatedOn from dbo.Train where CheckStatus=1 and Train.RealEndTime>(select GETDATE()))as temptable)as temptable2
where temptable2.tempNum>(@pageIndex-1)*@pageSize and temptable2.tempNum<=(@pageIndex*@pageSize)
return @rowCount
END存储过程
新建临时表,除所需要的列以外,新建int值的ID列,值为自增长
插入所有数据以后,那你取分页值的时候,只用where就行。
@pageSize int,
@pageIndex int
AS
BEGIN
SET NOCOUNT ON;
declare @rowCount intselect ROW_NUMBER()over (order by #temptable.CreatedOn desc)as tempNum, * into #temptable from
(select 'News' as , News.NewsID,News.NewsTitle,News.CreatedOn from dbo.News where CategoryID ='8b041bf4-24bf-4b35-85c7-f72717dc5752'
union all
select 'Activity' as , Activities.ActivityID,Activities.Subject,Activities.CreatedOn from dbo.Activities where Activities.CheckStatus=1 and IsDeleted=0 and Activities.RealEndTime>(select GETDATE())
union all
select 'Train' as , Train.TrainID,Train.Subject,Train.CreatedOn from dbo.Train where CheckStatus=1 and Train.PlannedEndTime>(select GETDATE()))as #temptableselect @rowCount=COUNT(*) from #temptableselect * from #temptable where #temptable.tempNum>(@pageIndex-1)*@pageSize and #temptable.tempNum<=(@pageIndex*@pageSize)return @rowCount
END