改写一个sql server 的过程,增加参数,屡试多次才成功
来源:互联网 发布:plc与单片机的3000介绍 编辑:程序博客网 时间:2024/06/11 02:51
ALTER procedure [dbo].[GetAllInUserCnt](@equiplist varchar(500))
as
declare @dt varchar(10)
declare @TmpStr varchar(100),@i int
set @dt=convert(varchar(10),getdate(),120)
declare @sqlstr varchar(2000)
--当天所有记录
--#代表局部临时表,##代表全局临时表
set @sqlstr = 'select * into ##DayDoorRecord from Door_Record a where errcode=0 and convert(varchar(10),dt,120)='''+ @dt+ '''' + @equiplist
PRINT @sqlstr
execute(@sqlstr)
--select * into #DayDoorRecord from Door_Record a where errcode=0 and convert(varchar(10),dt,120)=@dt and a.equno in (' + @equiplist + ')
--select * into #DayDoorRecord from Door_Record where errcode=0 and convert(varchar(10),dt,120)=@dt
select max(cardid+convert(varchar(20),dt,120)) as tmpstr into #TmpRecord from ##DayDoorRecord group by cardid
--当天最后一笔记录
select * into #DayMaxRecord from ##DayDoorRecord where cardid+convert(varchar(20),dt,120) in (select tmpstr from #TmpRecord)
--取天最后一笔记录的人员卡号
select cardid into #TmpUserInfo from #DayMaxRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=0)
select '900'as id,count(*) as cnt from #TmpUserInfo
union
select '700',count(*) from ##DayDoorRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=0)
union
select '800',count(*) from ##DayDoorRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=1)
drop table [##DayDoorRecord]
as
declare @dt varchar(10)
declare @TmpStr varchar(100),@i int
set @dt=convert(varchar(10),getdate(),120)
declare @sqlstr varchar(2000)
--当天所有记录
--#代表局部临时表,##代表全局临时表
set @sqlstr = 'select * into ##DayDoorRecord from Door_Record a where errcode=0 and convert(varchar(10),dt,120)='''+ @dt+ '''' + @equiplist
PRINT @sqlstr
execute(@sqlstr)
--select * into #DayDoorRecord from Door_Record a where errcode=0 and convert(varchar(10),dt,120)=@dt and a.equno in (' + @equiplist + ')
--select * into #DayDoorRecord from Door_Record where errcode=0 and convert(varchar(10),dt,120)=@dt
select max(cardid+convert(varchar(20),dt,120)) as tmpstr into #TmpRecord from ##DayDoorRecord group by cardid
--当天最后一笔记录
select * into #DayMaxRecord from ##DayDoorRecord where cardid+convert(varchar(20),dt,120) in (select tmpstr from #TmpRecord)
--取天最后一笔记录的人员卡号
select cardid into #TmpUserInfo from #DayMaxRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=0)
select '900'as id,count(*) as cnt from #TmpUserInfo
union
select '700',count(*) from ##DayDoorRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=0)
union
select '800',count(*) from ##DayDoorRecord
where equNo+cast(doorid as varchar(10)) in (select equid+cast(doorid as varchar(10)) from doorinfo where ioflag=1)
drop table [##DayDoorRecord]
阅读全文
0 0
- 改写一个sql server 的过程,增加参数,屡试多次才成功
- 自己改写的一个Sql Server 2000的分页存储过程
- 一个sql语句的改写
- SQL Server 存储过程的创建参数
- 自动增加的存储过程函数(SQL Server)
- 如何才算一个成功的seoer
- VFP如何调用一个带输出参数的Sql Server存储过程?
- 给存储过程加上一个是不是去重复的参数(sql server数据库)
- SQL中存储过程的参数怎样才做可以传给SELECT FROM 语句中做表名
- SQL Server存储过程 对数组参数的循环处理
- sql server不可不知的系统参数,存储过程,方法
- Sql Server存储过程数组类型参数的变通方法
- SQL Server存储过程 对数组参数的循环处理
- SQL Server存储过程 对数组参数的循环处理
- SQL server得到储存过程参数的方法
- SQL 简单的存储过程分页 改写方法
- 一个非常不友好的SQL的优化改写
- SQL SERVER 到 MYSQL 迁移存储过程代码改写详细对照表
- 在ASP.NET MVC(Razor)中使用avalon2.x,@字符冲突的不靠谱解决方案
- 【1701H1】【穆晨】【171104】连续第二十五天总结2
- Java编程思想第四版第二章练习
- python面向对象初探
- One Person Game ZOJ
- 改写一个sql server 的过程,增加参数,屡试多次才成功
- java程序运行时对象是怎么进行放置安排? 内存是如何分配的呢?
- 超级玛丽输出
- 获取时间
- muduo库源码学习(base)ThreadLocalSingleton
- switch开关语句
- HDU-2010(水仙花数)
- npm ERR!errno -4048
- 把pinpoint编译环境做成Docker镜像文件