本文介绍了如何创建该表的索引?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
USE [HJGINFODB]
GO
/******
object:Table [dbo].[TB_FB_EX]
scritp:09/19/2010 21:16:39
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_FB_EX](
[ID] [bigint] NULL,
[CITY_ID] [int] NOT NULL,
[QU_ID] [int] NOT NULL,
[TYPE_ID] [int] NOT NULL,
[TYPE_PID] [int] NOT NULL,
[STITLE] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[JG] [int] NOT NULL,
[INSTIME] [datetime] NULL CONSTRAINT [DF_TB_FB_EX_INSTIME] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [HJGINFODB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[f_div_TB_FB_EX]
@CITY_ID int,---------city of ID,default 0
@QU_ID int,-----------area ID,default 0
@TYPE_PID int,--------main type ID,default 0
@TYPE_ID int,---------small type ID,default 0
@PageIndex Int,-------Page Index
@PageSize Int,--------Page size
@ReturnRecordCount int out---row count
as
/*
declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,1,0,1,10,@ReturnRecordCount
declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571003,2,0,0,10,@ReturnRecordCount
declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,0,30,0,10,@ReturnRecordCount
declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571008,0,30,0,10,@ReturnRecordCount
*/
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex+1
SET @PageUpperBound = @PageLowerBound + @PageSize-1
--http://localhost/hangzhou/gongzuo/ 一级
if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME ,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
--http://localhost/hangzhou/jianzhiduangong/ 二级
else if @CITY_ID<>0 and @TYPE_ID<>0 and @QU_ID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID<>0
--http://localhost/hangzhou/gongzuo/571004/
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID ) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID<>0 and @QU_ID<>0
--http://localhost/hangzhou/dianyuan/571006/
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID=0 and @QU_ID=0 and @TYPE_PID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
推荐答案
这篇关于如何创建该表的索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!