对sql server 一些操作方法

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