模糊查询的存储过程
来源:互联网 发布:淘宝产品详情页素材 编辑:程序博客网 时间:2024/05/16 18:03
USE [YanShu_IndexDW]
GO
/****** Object: StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord] Script Date: 2017/9/19 19:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetZTCStandardPromotionPlanKeyWord]
@ParamName NVARCHAR(100), --模糊查询
@PageNum INT --页数
AS
DECLARE @SQL NVARCHAR(4000) ,
@SQLCount NVARCHAR(500) ,
@SelectColumns NVARCHAR(500) ,
@TmpStartRow NVARCHAR(10) ,
@TmpPageSize NVARCHAR(10)
BEGIN
SELECT DISTINCT
[keyWord] AS 关键词 ,
CAST([computerGrade] AS INT) AS 计算机质量分 ,
CAST([phoneGrade] AS INT) AS 移动质量分 ,
[computerRanking] AS 计算机排名 ,
[phoneRanking] AS 移动排名 ,
CAST(REPLACE([impressions], ',', '') AS INT) AS 展现量 ,
CAST(REPLACE([clickVolume], ',', '') AS INT) AS 点击量 ,
CAST([clickRate] AS FLOAT) AS 点击率 ,
CAST([expenditure] AS FLOAT) AS 花费 ,
CAST([directDealMoney] AS FLOAT) AS 直接成交金额 ,
CAST([meanClick] AS FLOAT) AS 平均点击花费 ,
CAST([totalVolume] AS INT) AS 总成交笔数 ,
CAST([averageShoWranking] AS INT) AS 平均展现排名 ,
CAST([inputOutput] AS FLOAT) AS 投入产出比 ,
CAST([directDealNum] AS INT) AS 直接成交笔数 ,
CAST([totalTurnover] AS FLOAT) AS 总成交金额 ,
CAST([clickConversion] AS FLOAT) AS 点击转化率 ,
CAST([directTrolleyNum] AS INT) AS 直接购物数 ,
CAST([enshrineComdityNum] AS INT) AS 收藏宝贝数 ,
CAST([thousandShow] AS FLOAT) AS 千次展现花费 ,
CAST([indirectTurnover] AS FLOAT) AS 间接成交金额 ,
CAST([indirectDealNum] AS INT) AS 间接成交笔数 ,
CAST([enshrineStoreNum] AS INT) AS 收藏店铺数 ,
CAST([totalCollectionNum] AS INT) AS 总收藏数 ,
CAST([toalTrolleyNum] AS INT) AS 总购物车数 ,
CAST([indirectTrolleyNum] AS INT) AS 间接购物车数
INTO #TmpZTC_standard_promotion_plan_keyword_query
FROM [YanShu_IndexDW].[ztc].[ZTC_standard_promotion_plan_KeyWord](NOLOCK)
SET @TmpStartRow = ( @PageNum - 1 ) * 10
SET @TmpPageSize = 10
SELECT @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query'
Select @SQL='select top '+@TmpPageSize+'* from (SELECT row_number() OVER(ORDER BY 关键词)AS RowNumber,* from TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName +'%'' ) a WHERE RowNumber>'+@TmpStartRow
PRINT @SQL
EXEC(@SQLCount)
EXEC(@SQL)
END
exec [SP_GetZTCStandardPromotionPlanKeyWord] '喇叭裤 女 长裤' ,1
GO
/****** Object: StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord] Script Date: 2017/9/19 19:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetZTCStandardPromotionPlanKeyWord]
@ParamName NVARCHAR(100), --模糊查询
@PageNum INT --页数
AS
DECLARE @SQL NVARCHAR(4000) ,
@SQLCount NVARCHAR(500) ,
@SelectColumns NVARCHAR(500) ,
@TmpStartRow NVARCHAR(10) ,
@TmpPageSize NVARCHAR(10)
BEGIN
SELECT DISTINCT
[keyWord] AS 关键词 ,
CAST([computerGrade] AS INT) AS 计算机质量分 ,
CAST([phoneGrade] AS INT) AS 移动质量分 ,
[computerRanking] AS 计算机排名 ,
[phoneRanking] AS 移动排名 ,
CAST(REPLACE([impressions], ',', '') AS INT) AS 展现量 ,
CAST(REPLACE([clickVolume], ',', '') AS INT) AS 点击量 ,
CAST([clickRate] AS FLOAT) AS 点击率 ,
CAST([expenditure] AS FLOAT) AS 花费 ,
CAST([directDealMoney] AS FLOAT) AS 直接成交金额 ,
CAST([meanClick] AS FLOAT) AS 平均点击花费 ,
CAST([totalVolume] AS INT) AS 总成交笔数 ,
CAST([averageShoWranking] AS INT) AS 平均展现排名 ,
CAST([inputOutput] AS FLOAT) AS 投入产出比 ,
CAST([directDealNum] AS INT) AS 直接成交笔数 ,
CAST([totalTurnover] AS FLOAT) AS 总成交金额 ,
CAST([clickConversion] AS FLOAT) AS 点击转化率 ,
CAST([directTrolleyNum] AS INT) AS 直接购物数 ,
CAST([enshrineComdityNum] AS INT) AS 收藏宝贝数 ,
CAST([thousandShow] AS FLOAT) AS 千次展现花费 ,
CAST([indirectTurnover] AS FLOAT) AS 间接成交金额 ,
CAST([indirectDealNum] AS INT) AS 间接成交笔数 ,
CAST([enshrineStoreNum] AS INT) AS 收藏店铺数 ,
CAST([totalCollectionNum] AS INT) AS 总收藏数 ,
CAST([toalTrolleyNum] AS INT) AS 总购物车数 ,
CAST([indirectTrolleyNum] AS INT) AS 间接购物车数
INTO #TmpZTC_standard_promotion_plan_keyword_query
FROM [YanShu_IndexDW].[ztc].[ZTC_standard_promotion_plan_KeyWord](NOLOCK)
SET @TmpStartRow = ( @PageNum - 1 ) * 10
SET @TmpPageSize = 10
SELECT @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query'
Select @SQL='select top '+@TmpPageSize+'* from (SELECT row_number() OVER(ORDER BY 关键词)AS RowNumber,* from TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName +'%'' ) a WHERE RowNumber>'+@TmpStartRow
PRINT @SQL
EXEC(@SQLCount)
EXEC(@SQL)
END
exec [SP_GetZTCStandardPromotionPlanKeyWord] '喇叭裤 女 长裤' ,1
阅读全文
0 0
- 模糊查询的存储过程
- 存储过程模糊查询~
- 模糊查询存储过程
- 模糊查询的通用存储过程
- MaBatis的模糊查询与存储过程
- 分页模糊查询存储过程
- 模糊查询(存储过程)
- oracle 存储过程模糊查询
- 存储过程排序+模糊查询
- ASP.NET利用存储过程实现模糊查询的心得
- 关于SQLServer存储过程动态拼接,模糊查询的处理
- 如何编写一个带模糊查询的存储过程
- sql server 存储过程 实现模糊查询
- 存储过程实现模糊查询分页
- 模糊匹配查询用存储过程实现
- oracle 存储过程支持模糊查询
- 存储过程带参数模糊查询
- 存储过程实现模糊查询问题
- jsp出现The superclass "javax.servlet.http.HttpServlet" was not found on the Java Build Path
- 结构型模式之适配器模式
- hrbust 1609 Windows安全警报
- 将java RDD结果写入Hive表中
- Android 常用工具类
- 模糊查询的存储过程
- listview
- MySQL5.6.25升级MySQL5.7.15
- list、set、map
- C语言实现单链表面试题---基础篇
- NodeMCU(ESP8266)开发笔记
- Xlistview加载更多
- RedisComponent
- 51nod--1049 最大子段和