复杂报表的存储过程

USE [MoponZhongYingGroup_cs]
GO

/****** Object: StoredProcedure [dbo].[TempCountCimetomg] Script Date: 12/24/2014 15:24:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--EXEC TempCountCimetomg '','','2014-12-19','2014-12-22','2014-12-19',0

alter procedure [dbo].[TempCountCimetomg]
(@CinemaNo VARCHAR(30), --影院编号
@CinemaName VARCHAR(50), --影院名称
@StartDate VARCHAR(20), --开始时间
@EndDate VARCHAR(20), --结束时间
@SearchDate VARCHAR(20),--查询时间
@isGroupClient INT --是否按客户端分类
)

AS
BEGIN
IF(len(@StartDate)<1 OR len(@EndDate)<1) --必须输入时间范围
RETURN 0

DECLARE @sqlIf VARCHAR(4000)
DECLARE @sql VARCHAR(max)
DECLARE @PayChannelNo VARCHAR(20) --支付方式
DECLARE @PayChannelList VARCHAR(500) --支付方式列表

SET @PayChannelList='TCARDPAY,USERCARD,CPTPAY,WEB,CLIENT,ACT,All'
--定义表
DECLARE @PayChannelTb TABLE (
PayChannelNo VARCHAR(20) NOT NULL
)
--调用control.f_splitToTable 函数将数据插到@PayChannelTb表中去
INSERT INTO @PayChannelTb SELECT col FROM control.f_splitToTable(@PayChannelList,',') fstt

SELECT oe.OrderNo,ae.AreaName,ce.CinemaNo,ce.OrganizationName,oe.CreateTime,ode.showType,
ode.PiaoFangPrice,ode.OrderType,oe.ClientNo,oe.ClientName,isnull(th.IsVipHall,0) IsVipHall,
ode.RealSinglePrice,ode.SubsidyPrice,
ode.ChargeSinglePrice ,oe.PayTime,oe.[Status]
,[PayChannelNo]=
(case
when (SELECT SUM(ExchangeFlag) FROM control.tb_OrderDetail WHERE OrderNo=oe.OrderNo GROUP BY OrderNo )>0
THEN 'CPTPAY'
ELSE
--这里也可以写查询语句
(SELECT top 1 tpod.PayChannelNo
FROM control.tb_PayOrder tpo INNER JOIN control.tb_PayOrderDetail tpod ON
tpo.PayOrderNo=tpod.PayOrderNo
AND tpod.[Status]=2
AND tpo.[Status]=3
AND tpo.OrderNo=ode.OrderNo
AND tpod.PayChannelNo<>'CPTPAY')
END)
-- 现将所有数据放到临时表#temptb中去 into #temptb
INTO #temptb
FROM control.tb_Order oe
inner join control.tb_OrderDetail ode on oe.OrderNo=ode.OrderNo
inner join control.tb_Cinema ce on oe.CinemaNo=ce.CinemaNo
inner join control.tb_Organization oze on ce.OrganizationNo=oze.OrganizationNo
inner join base.tb_Area ae on oze.CityNo=ae.AreaNo
--满足多条件用And
LEFT JOIN control.tb_Hall th ON th.[Status]=1 AND th.HallNo=ode.HallNo
WHERE oe.TicketStatus =3
AND ode.OrderType IN (1,2)
AND oe.[Status] in (1,5,6)
AND oe.PayStatus=3
--注意这里的写法.
AND oe.CinemaNo=(case when len(@CinemaNo)=0 then oe.CinemaNo else @CinemaNo end)
AND oe.CinemaName like (case when len(@CinemaName)=0 then oe.CinemaName else '%'+@CinemaName+'%' end)
AND oe.CreateTime >= @StartDate+' 06:00:00'
AND oe.CreateTime < CONVERT(varchar(10),dateadd(day,1,@EndDate),120)+' 06:00:00'
--
select * from #temptb;
select 'A';
--这里是额外添加的
--原
SELECT '原' DataType, * into #temptbOriginal FROM #temptb
WHERE [Status]=1
--补
SELECT '补' DataType,* into #temptbSupplement FROM #temptb
WHERE CONVERT(VARCHAR(10),PayTime,120) > CONVERT(VARCHAR(10),CreateTime,120) AND CONVERT(VARCHAR(10),PayTime,120)<=@SearchDate
--退
SELECT '退' DataType,* into #temptbRefund FROM #temptb WHERE [Status] IN (5,6)

SELECT @sqlIf='',@sql=''

DECLARE @DataTypeList VARCHAR(200)
DECLARE @DataType VARCHAR(200)
SET @DataTypeList='Original,Supplement,Refund'
DECLARE @DataTypeTb TABLE (
DataType VARCHAR(20) NOT NULL
)

--while Exists 一直执行到不存在为止
--if Exists 执行一次
WHILE EXISTS(SELECT TOP 1 * FROM @PayChannelTb pct) --支付方式循环开始
--注意while循环的方式
BEGIN
SELECT @sql=''
---循环取值@PayChannelNo=（TCARDPAY,USERCARD,CPTPAY,WEB,CLIENT,ACT,All）
SELECT TOP 1 @PayChannelNo=[PayChannelNo] FROM @PayChannelTb pct

--二重循环
DELETE @DataTypeTb
INSERT INTO @DataTypeTb SELECT col FROM control.f_splitToTable(@DataTypeList,',') fstt
WHILE EXISTS(SELECT TOP 1 * FROM @DataTypeTb ) --数据类型（DataType）循环开始
BEGIN
---循环取值原退补
SELECT TOP 1 @DataType=[DataType] FROM @DataTypeTb

IF(@PayChannelNo='WEB')
BEGIN
SET @sqlIf= ' and PayChannelNo IN (''ALIPAY10'',''UPOP6'') and OrderType=1 and ClientNo <>''C10000006'''
END
else IF(@PayChannelNo='CLIENT')
BEGIN
SET @sqlIf= ' and PayChannelNo IN (''ALIPAY10'',''UPOP6'') and OrderType=1 and ClientNo=''C10000006'''
END
else IF(@PayChannelNo='ACT')
BEGIN
SET @sqlIf= ' and OrderType=2'
END
ELSE IF(@PayChannelNo='All')
BEGIN
SET @sqlIf= ' and PayChannelNo not in (''THIRDPAY'',''UPMP3'')'
END
ELSE
BEGIN
SET @sqlIf= ' and PayChannelNo='''+@PayChannelNo+''' and OrderType=1 '
END

SET @sql=@sql+' SELECT DataType,CinemaNo,OrganizationName as CinemaName '
IF(@isGroupClient=1)
BEGIN
set @sql=@sql+',ClientName,ClientNo';
END
ELSE
BEGIN
set @sql=@sql+' ,''合计'' as ClientName,'''' ClientNo ';
END

SET @sql=@sql+',['+@PayChannelNo+'_2D]=SUM(CASE WHEN showType=''2D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_3D]=SUM(CASE WHEN showType=''3D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_IMAX2D]=SUM(CASE WHEN showType=''IMAX 2D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_IMAX3D]=SUM(CASE WHEN showType=''IMAX 3D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_DMAX2D]=SUM(CASE WHEN showType=''DMAX 2D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_DMAX3D]=SUM(CASE WHEN showType=''DMAX 3D'' and IsVipHall=0 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,['+@PayChannelNo+'_Total]=count(1)
,['+@PayChannelNo+'_SettlementPrice]=sum(RealSinglePrice)
,['+@PayChannelNo+'_Reserve_G]=0
,['+@PayChannelNo+'_Reserve_C]=0
,['+@PayChannelNo+'_Reserve_P]=sum(isnull(SubsidyPrice,0))
,['+@PayChannelNo+'_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,['+@PayChannelNo+'_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptb'+@DataType+'
where 1=1 '+@sqlIf+'
GROUP BY DataType,CinemaNo,OrganizationName '
--#temptbOriginal #temptbSupplement #temptbRefund

IF(@isGroupClient=1)
BEGIN
set @sql=@sql+',ClientNo,ClientName'
END
--set @sql=@sql+' order by CinemaNo'

DELETE @DataTypeTb WHERE [DataType]=@DataType
IF(EXISTS(SELECT TOP 1 * FROM @DataTypeTb ))
set @sql=@sql+' UNION ALL '
END --数据类型（DataType）循环结束

print @sql;
exec (@sql);

DELETE @PayChannelTb WHERE [PayChannelNo]=@PayChannelNo

END --支付方式循环结束

DROP TABLE #temptb
DROP TABLE #temptbOriginal
DROP TABLE #temptbSupplement
DROP TABLE #temptbRefund

END

---------------输出的print @sql

(7 行受影响)

(77 行受影响)

(1 行受影响)

(77 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo='TCARDPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo='TCARDPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo='TCARDPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(3 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo='USERCARD' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo='USERCARD' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo='USERCARD' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo='CPTPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo='CPTPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo='CPTPAY' and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[WEB_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo <>'C10000006'
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[WEB_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo <>'C10000006'
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[WEB_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo <>'C10000006'
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo='C10000006'
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo='C10000006'
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo IN ('ALIPAY10','UPOP6') and OrderType=1 and ClientNo='C10000006'
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[ACT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[ACT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[ACT_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName

(0 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[All_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo not in ('THIRDPAY','UPMP3')
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[All_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo not in ('THIRDPAY','UPMP3')
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,'合计' as ClientName,'' ClientNo ,[All_2D]=SUM(CASE WHEN showType='2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType='3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType='IMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType='IMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType='DMAX 2D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType='DMAX 3D' and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo not in ('THIRDPAY','UPMP3')
GROUP BY DataType,CinemaNo,OrganizationName

(4 行受影响)

(1 行受影响)

--------------------------------------------EasyUI前端的显示是：

fun：函数

sp_helptext 'control.SplitString'
-----------------------------------------
CREATE FUNCTION control.SplitString(@Long_str varchar(8000),@split_str varchar(100))
RETURNS @tmp TABLE(
short_str varchar(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str,@Long_str)=1
SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
ELSE
SET @long_str_Tmp=@Long_str
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1
SET @long_str_Tmp=@long_str_Tmp+@split_str
ELSE
SET @long_str_Tmp=@long_str_Tmp
IF CHARINDEX(@split_str,@long_str_Tmp)=0
Insert INTO @tmp select @long_str_Tmp
ELSE
BEGIN
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)+@split_str_length
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str
END
END
RETURN
END
------------------------------------------

sp_helptext 'control.f_split'

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

CREATE function control.f_split(@c varchar(2000),@split varchar(2))

returns @t table(id int,col varchar(20))
as
begin
DECLARE @i int
SET @i=0
while(charindex(@split,@c) <> 0)
begin
SET @i = @i+1
insert @t(id,col) values (@i,substring(@c,1,charindex(@split,@c)-1))
set @c= stuff(@c,1,charindex(@split,@c), '')
end
return
end

-----------------------------------------------------------------------------------
CREATE function [control].[f_splitToTable](@c varchar(max),@split varchar(2))
returns @t table(id int identity(1,1) primary key,col varchar(max))
as
begin
declare @len int
select @len = count(short_str) from control.SplitString(@c,@split)

insert into @t
select short_str from control.SplitString(@c,@split)
return
end

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

sp_helptext 'control.SplitString'

CREATE FUNCTION control.SplitString(@Long_str varchar(8000),@split_str varchar(100))
RETURNS @tmp TABLE(
short_str varchar(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str,@Long_str)=1
SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
ELSE
SET @long_str_Tmp=@Long_str
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1
SET @long_str_Tmp=@long_str_Tmp+@split_str
ELSE
SET @long_str_Tmp=@long_str_Tmp
IF CHARINDEX(@split_str,@long_str_Tmp)=0
Insert INTO @tmp select @long_str_Tmp
ELSE
BEGIN
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)+@split_str_length
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str
END
END
RETURN
END

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

--sp_helptext 'control.mp_commonPagination'
CREATE PROC [control].[mp_commonPagination]
@columns VARCHAR(500) , --要显示的列名，用逗号隔开
@tableName VARCHAR(8000) , --要查询的表名
@orderColumnName VARCHAR(100) , --排序的列名
@order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc
@where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1
@pageIndex INT , --当前页索引
@pageSize INT , --页大小(每页显示的记录条数)
--@pageCount INT OUTPUT, --总页数，输出参数
@CountNum Int OUTPUT--总条数
AS
BEGIN
DECLARE @pageCount INT;
DECLARE @sqlRecordCount NVARCHAR(4000) --得到总记录条数的语句
DECLARE @sqlSelect NVARCHAR(4000) --查询语句
SET @sqlRecordCount = N'select @recordCount=count(*) from ('+ @tableName+' ) AS PageCount'
DECLARE @recordCount INT --保存总记录条数的变量
EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',@recordCount OUTPUT
SET @CountNum=@recordCount;
PRINT('调用分页过程：'+@sqlRecordCount)
--动态 sql 传参
IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除
SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
ELSE --如果总记录条数不能被页大小整除
SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
SET @sqlSelect = N'select ' + @columns
+ ' from ( select row_number() over (order by ' + @orderColumnName
+ ' ' + @order + ') as tempid,* from (' + @tableName + ') AS PageCount) as tempTableName where tempid between '
+ STR(( @pageIndex - 1 ) * @pageSize + 1) + ' and '
+ STR(@pageIndex * @pageSize)
PRINT('调用分页过程2:'+@sqlSelect);
EXEC (@sqlSelect) --执行动态Sql
END

posted on 2014-12-24 17:59 chengjunde 阅读(343) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

chengjunde

复杂报表的存储过程

导航

公告