存储过程排序+模糊查询
来源:互联网 发布:魔兽7..0mac版插件 编辑:程序博客网 时间:2024/05/21 06:40
USE [YanShu_IndexDW]
GO
/****** Object: StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord] Script Date: 2017/9/20 8:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetZTCSearchPromotionPlanKeyWord]
@SellerId BIGINT ,--店铺ID
@ParamName NVARCHAR(100), --模糊查询
@SortName NVARCHAR(100) ,--排序字段名称
@SortType NVARCHAR(50) ,--排序规则
@PageNum INT --页数
AS
DECLARE @SQL NVARCHAR(4000) ,
@SQLCount NVARCHAR(500) ,
@SelectColumns NVARCHAR(500) ,
@TmpStartRow NVARCHAR(10) ,
@TmpPageSize NVARCHAR(10)
BEGIN
IF LEN(@SortName) = 0
BEGIN
SELECT @SortName = '点击量' ,
@SortType = 'desc'
END
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 @SQL='select * from emp where name like ''%'+ @name +'%'''
SELECT @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName+'%'' '
Select @SQL='select top '+@TmpPageSize+'* from (SELECT row_number() OVER(ORDER BY ' + @SortName + ' ' + @SortType + ' )AS RowNumber,* from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName +'%'' ) a WHERE RowNumber>'+@TmpStartRow
PRINT @SQL
EXEC(@SQL)
EXEC(@SQLCount)
END
GO
/****** Object: StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord] Script Date: 2017/9/20 8:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetZTCSearchPromotionPlanKeyWord]
@SellerId BIGINT ,--店铺ID
@ParamName NVARCHAR(100), --模糊查询
@SortName NVARCHAR(100) ,--排序字段名称
@SortType NVARCHAR(50) ,--排序规则
@PageNum INT --页数
AS
DECLARE @SQL NVARCHAR(4000) ,
@SQLCount NVARCHAR(500) ,
@SelectColumns NVARCHAR(500) ,
@TmpStartRow NVARCHAR(10) ,
@TmpPageSize NVARCHAR(10)
BEGIN
IF LEN(@SortName) = 0
BEGIN
SELECT @SortName = '点击量' ,
@SortType = 'desc'
END
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 @SQL='select * from emp where name like ''%'+ @name +'%'''
SELECT @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName+'%'' '
Select @SQL='select top '+@TmpPageSize+'* from (SELECT row_number() OVER(ORDER BY ' + @SortName + ' ' + @SortType + ' )AS RowNumber,* from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName +'%'' ) a WHERE RowNumber>'+@TmpStartRow
PRINT @SQL
EXEC(@SQL)
EXEC(@SQLCount)
END
阅读全文
0 0
- 存储过程排序+模糊查询
- 存储过程模糊查询~
- 模糊查询存储过程
- 分页模糊查询存储过程
- 模糊查询(存储过程)
- oracle 存储过程模糊查询
- 模糊查询的存储过程
- sql server 存储过程 实现模糊查询
- 存储过程实现模糊查询分页
- 模糊匹配查询用存储过程实现
- 模糊查询的通用存储过程
- oracle 存储过程支持模糊查询
- 存储过程带参数模糊查询
- 存储过程实现模糊查询问题
- MaBatis的模糊查询与存储过程
- ASP.NET中利用存储过程实现模糊查询
- ASP.NET中利用存储过程实现模糊查询
- ASP.NET中利用存储过程实现模糊查询
- SpringBoot学习笔记之打包部署
- SpringMVC Controller介绍及常用注解
- Node.js模块全局安装路径配置
- 蝉道Bug管理工具的环境搭建
- Windows7下安装与破解IntelliJ IDEA2017
- 存储过程排序+模糊查询
- 屏蔽JS错误
- Android中关于JNI 的学习(一)对于JNIEnv的一些认识
- android.mk 详解
- ActiveMQ系列—JMS规范中的几个基本概念
- MySQL 配置记录
- 代码获取颜色的方法
- Codeforces Round #435 (Div. 2) C. Mahmoud and Ehab and the xor
- 机器学习sklearn参数解释(GDBT+XGBOOST)