if (exists (select * from sys.objects where name = 'Page_UserInfo')) drop proc Page_UserInfo --判断存储过程是否存在，存在则删除然后重建。
go
create proc Page_UserInfo --创建存储过程
@name nvarchar(255),--用户名
@pageindex int,--第几页
@pagesize int--一页多少条
as
set nocount on; --不返回计数，提高应用程序性能
begin --开始
declare @pagebefore int;--创建页数
declare @pagerear int;--创建页数
declare @condition nvarchar(2000); --创建where条件
set @pagebefore=@pagesize*@pageindex; --起始页
set @pagerear=@pagebefore+@pagesize;--结束页
set @condition=' where 1=1 ';
if(@name<>'')
set @condition=@condition+' and name like ''%'+@name+'%''';
--创建一个虚拟表插入UserInfo表数据
--获取分页数据
--获取总数
exec('
declare @table table(
iid int identity,
Id int,
Name nvarchar(20),
Sex int,
Age int,
Birthday datetime
)
insert @table
select * from UserInfo '+@condition+' order by Id desc
select * from @table where iid>'+@pagebefore+' and iid<='+@pagerear+'
select count(*) as rows from @table;');
end;--结束
调用方式
EXEC Page_UserInfo '' ,1,10 (赋值:用户名,页数,一页多少条)
USE [newHRP]
GO
/****** Object: StoredProcedure [dbo].[Pro_TradeRecord] Script Date: 11/10/2020 11:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure Pro_TradeRecord
@StartTime datetime,@EndTime datetime
as
if @StartTime=''
begin
set @StartTime=DateAdd(MM,DATEDIFF(MM,0,getdate()),0)
end
if @EndTime=''
begin
set @EndTime=DateAdd(MI,-1,DateAdd(MM,DATEDIFF(MM,0,getdate())+1,0))
end
else
begin
set @EndTime=CONVERT(char(10),@EndTime,23)+' 23:59:00'
end
select CS_TRTradeDate as 日期,ZaoCan as 早餐,ZaoCanRenShu as 早餐人数,ZhongCan as 中餐,ZhongCanRenShu as 中餐人数,WangCan as 晚餐,WangCanRenShu as 晚餐人数,ZaoCanRenShu+ZhongCanRenShu+WangCanRenShu as 用餐人数统计,ZaoCan+ZhongCan+WangCan as 用餐金额统计,ChaoShiJinE as 超市金额,ChaoShiRenShu 超市人数,TanFenJinE as 汤粉面档金额,TanFenRenShu as 汤粉面档人数,ZaoCan+ZhongCan+WangCan+ChaoShiJinE+TanFenJinE as 合计
from
(
select
convert(char(10),CS_TRTradeTime,23) as CS_TRTradeDate,
sum(case when a.CS_TDName = '早餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as ZaoCan,
sum(case when a.CS_TDName = '早餐'and c.Sys_RGName='饭堂' then 1 else 0 end) as ZaoCanRenShu,
sum(case when a.CS_TDName = '中餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as ZhongCan,
sum(case when a.CS_TDName = '中餐' and c.Sys_RGName='饭堂' then 1 else 0 end) as ZhongCanRenShu,
sum(case when a.CS_TDName = '晚餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as WangCan,
sum(case when a.CS_TDName = '晚餐' and c.Sys_RGName='饭堂' then 1 else 0 end) as WangCanRenShu,
sum(case c.Sys_RGName when '超市' then a.CS_TRTradeMoney else 0 end) as ChaoShiJinE,
sum(case c.Sys_RGName when '超市' then 1 else 0 end) as ChaoShiRenShu,
sum(case c.Sys_RGName when '汤粉面档' then a.CS_TRTradeMoney else 0 end) as TanFenJinE,
sum(case c.Sys_RGName when '汤粉面档' then 1 else 0 end) as TanFenRenShu
from CS_TradeRecord a left join sys_equipment b on a.Sys_EquipmentID=b.Sys_EquipmentID
left join Sys_Region c on b.Sys_RegionID=c.Sys_RegionID
where CS_TRTradeTime between @StartTime and @EndTime
GROUP BY convert(char(10),CS_TRTradeTime,23)
)T
