1.在数据库处理统计数据 刚开始是在数据库处理的，后来发现在业务层会更好些 不过还是放这以后用的着
--查询单个员工工作效率 if exists(select * from sysobjects where name='sp_SelectEfficiency') drop proc sp_SelectEfficiency go create proc sp_SelectEfficiency @CpNumber varchar(20), @LastTime varchar(20), @LastTime2 varchar(20) as begin select @LastTime= convert(datetime,(convert(varchar,@LastTime)+' 00:00:00')) select @LastTime2= convert(datetime,(convert(varchar,@LastTime2)+' 23:59:59')) declare @table table ( CName varchar(20),--来电姓名 CountTime int, --来电次数 Time2 float, --在场时间 Time1 float , --在途时间 Efficiency float --工作效率 ) declare @xunhuan int select @xunhuan=count(*) from tblCheckedPersonInfo where 1=1 and (CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0) and(LastTime between @LastTime and @LastTime2) -- group by CpNumber while(@xunhuan>0) begin declare @name varchar(20) --员工姓名 declare @Number varchar(20) --根据不同的 @xunhuan 找出其员工工号 declare @count float --在场时间 小时数 declare @c int --来电次数 declare @countN int --在场时间 分钟数 declare @countN2 float --工作时间 declare @Efficiency float --工作效率 declare @int int set @int=1 --根据不同的@xunhuan 找出对应的姓名，工号 select @name=CpName,@Number=CpNumber from ( select CpID,CpName,CpNumber, (select count(*) from tblCheckedPersonInfo as e2 where e1.CpID >= e2.CpID and (e2.CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0) and(e2.LastTime between @LastTime and @LastTime2) ) as rownumber from tblCheckedPersonInfo as e1 where 1=1 and (e1.CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0) and(e1.LastTime between @LastTime and @LastTime2) ) as a where rownumber=@xunhuan --首先判断这条信息是否完整 即 要有离开宾馆时间 到达工厂时间 离开工厂时间
--根据工厂代码分组 查询在场时间和 select @countN=sum(a.shijian) from ( select FacNumber,datediff(n,min(LastTime),max(LastTime)) as shijian from tblCheckedPersonInfo where 1=1 and(CpNumber=@Number) and(LastTime between @LastTime and @LastTime2) and(CheckedType='1' or CheckedType='2') and IsEffect='1' group by FacNumber ) as a --根据 天分组 求出每天工作时间和 select @countN2=sum(shijian) from ( select datediff(n,min(LastTime),max(LastTime)) as shijian from tblCheckedPersonInfo where 1=1 and(CpNumber=@Number) and(LastTime between @LastTime and @LastTime2) and IsEffect='1' group by day(LastTime) ) as a -- select @count=convert(decimal(18,2),@countN/convert(numeric(8,2),60)) --在场小时数 select @countN2=convert(decimal(18,2),@countN2/convert(numeric(8,2),60)) --工作时间 select @c=count(*) from tblCheckedPersonInfo where 1=1 and(CpNumber=@Number) and(LastTime between @LastTime and @LastTime2) and IsEffect='1' --来电次数 select @Efficiency=convert(decimal(18,2),(@count/(convert(numeric(8,2),(datediff(dd,@LastTime,@LastTime2)+1))))) --工作效率 insert into @table --将上述结果插入到虚拟表@table中 select CName=@name,CountTime=@c,Time2=@count,Time1=@countN2-@count,Efficiency=@Efficiency set @xunhuan=@xunhuan-1 --循序-1 set @int=@int+1 end select distinct CName,CountTime,Time2,Time1,Efficiency from @table end
---------------------------------------------------------------------------------------------------
2.处理单个字段中的数据
如‘12,123,abc,ccd’
通过‘12’模糊查询后将其符合查找结果为
12
123
--将电话号码 放在gridview 中模糊查询 ， if exists(select * from sysobjects where name='sp_ChuliTel') drop proc sp_ChuliTel go create proc sp_ChuliTel @tel nvarchar(1000), @aa varchar(1000) as begin declare @table table (tele varchar(1000)) --创建临时表 if charindex( ',',@tel) = 0 --如果没有分隔符号插入数据 begin insert into @table select @tel end while charindex( ',',@tel) > 0 --有分隔符号 循环插入 begin insert into @table --逐个插入 select left(@tel,charindex( ',',@tel)-1) set @tel = right(@tel,len(@tel) - charindex( ',',@tel)) If charindex(',',@tel)=0 and @tel<>'' --将最后一条数据插入 insert into @table select @tel end select tele from @table where charindex(@aa, tele)>0 end
------------------------------------------------------------------------------------------------
3.触发器的操作
--触发器 当来电信息有一条记录 的根据当前的CpID 插入检验员姓名 组号 工厂名称 及判断来电号码与工厂地址是否一致 go --if exists(select * from tblCheckedPersonInfo where name='trg_tblCheckedPersonInfo') drop TRIGGER trg_tblCheckedPersonInfo go CREATE TRIGGER trg_tblCheckedPersonInfo ON tblCheckedPersonInfo FOR INSERT AS begin declare @CpID int declare @CpNumber nvarchar(50) declare @EmpName nvarchar(50) declare @EmpGroup varchar(100) declare @FacNumber nvarchar(100) declare @FacName nvarchar(100) declare @TeleNumber varchar(1000) declare @facname2 nvarchar(100) declare @CheckedType varchar(4) declare @id int declare @IsEffect varchar(1) declare @time datetime declare @count int declare @isSure varchar(10) -- declare @ID int select @CpNumber=CpNumber,@FacNumber=FacNumber,@CpID=CpID,@TeleNumber=TeleNumber,@CheckedType=CheckedType,@time=LastTime,@IsEffect=IsEffect,@isSure=isSure from inserted select @EmpName=EmpName,@EmpGroup=EmpGroup from tblEmployeesInfo where EmpNumber=@CpNumber select @FacName=FacName from tblFactoryInfo where FacNumber=@FacNumber select @facname2=FacNumber from tblFactoryInfo where charindex(@TeleNumber, TeleNumber)>0 --判断是否为加班 if((datename(weekday,getdate())='星期六') or (datename(weekday,getdate())='星期日')) begin if(charindex('A',@isSure)=0) begin update tblCheckedPersonInfo set isSure=isSure+'A' where CpID=@CpID end end --判断号码是否存在 if not exists(select * from tblFactoryInfo where charindex(@TeleNumber,isFacNumber)>0) begin if(charindex('B',@isSure)=0) begin update tblCheckedPersonInfo set isSure=isSure+'B' where CpID=@CpID end end --判断来电号码 与工厂地址是否一致
if(@facname2 is not null) begin if(@FacNumber<>@facname2) begin -- update tblCheckedPersonInfo set isGood='0' where CpID=@CpID if(charindex('C',@isSure)=0) begin update tblCheckedPersonInfo set isSure=isSure+'C' where CpID=@CpID end end end --判断来电信息 是否重复 -- select @id=CpID from tblCheckedPersonInfo -- where CpNumber=@CpNumber -- and FacNumber=@FacNumber -- and TeleNumber=@TeleNumber -- and CheckedType=@CheckedType -- and IsEffect='1' -- and @time<>LastTime -- if @id>0 -- begin -- update tblCheckedPersonInfo set IsEffect='2' where CpID=@id -- end
-- update tblCheckedPersonInfo set CpName=@EmpName,EmpGroup=@EmpGroup,FacName=@FacName where CpID=@CpID --将来电信息插入到tblEmpGroup中 -- select @count=count(*) from tblCheckedPersonInfo -- where CpNumber=@CpNumber -- and FacNumber=@FacNumber -- and TeleNumber=@TeleNumber -- and CheckedType=@CheckedType -- if(@count<=1) if(@IsEffect='1') begin If charindex(',',@EmpGroup)=0 and @EmpGroup<>'' begin insert into tblEmpGroup(CpNumber,FacNumber,EmpGroup,CheckedType,LastTime) select @CpNumber,@FacNumber,@EmpGroup,@CheckedType,@time end while charindex(',',@EmpGroup)>0 begin insert into tblEmpGroup(CpNumber,FacNumber,EmpGroup,CheckedType,LastTime) select @CpNumber,@FacNumber,left(@EmpGroup,charindex( ',',@EmpGroup)-1),@CheckedType,@time set @EmpGroup = right(@EmpGroup,len(@EmpGroup) - charindex( ',',@EmpGroup)) If charindex(',',@EmpGroup)=0 and @EmpGroup<>'' begin insert into tblEmpGroup(CpNumber,FacNumber,EmpGroup,CheckedType,LastTime) select @CpNumber,@FacNumber,@EmpGroup,@CheckedType,@time end end --根据来电信息 判断这条记录是否有效 end -- end
--去掉字符串两边的‘，’
declare @varchar varchar(10) declare @str varchar(max) set @str=',abc,' select @varchar=left(@str,1) if @varchar=',' select @str=right(@str,len(@str)-1) select @varchar=right(@str,1) if @varchar=',' select @str=left(@str,len(@str)-1) print @str
--检索数据库中所有表字段内容
declare @str varchar(100) set @str='张三' declare @s varchar(8000) declare tb cursor local for select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') print ''所在的表及字段: ['+b.name+'].['+a.name+']''' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb
declare @int int exec proc_page 'birdbaseinfo','*','english_name','1=1','asc',1,5, @int output
select top 10 * from table where 1=1
and id<(
select min(id) from (select top 10*1 id from table order by id desc) as a
)order by id desc
--oracle
select * from qyxx_qbs where 1=1 and jgdm<( select min(jgdm) from ( select jgdm from qyxx_qbs where BZRQ between to_date('2012-04-01','yyyy-MM-dd') and to_date('2012-04-30','yyyy-MM-dd') and rownum <= 10*1 order by jgdm desc ) a ) and rownum <= 10 order by jgdm desc
