sql 学习记录

1.  搜索统一字段中所有的同名记录
      select loginid from systemusers group by loginid having count(loginid)>1

2.  数据触发器应用：Insert  >   当数据为空时（NULL）将数据转变为同列中最大值加 1
 
CREATE trigger test_insert

on dbo._OA_HJXM

instead of insert

as

begin

  insert into _OA_HJXM (Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,Number) 

  select  Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,

      case when number is null

              1.then (select isnull(count(*),0)+1 from  _OA_HJXM)

              2.then (select isnull(max(number),0)+1 from  _OA_HJXM)

           else number

      end

 

 from inserted

end

on dbo._OA_HJXM

instead of insert

as

begin

  insert into _OA_HJXM (Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,Number) 

  select  Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,

      case when number is null

              1.then (select isnull(count(*),0)+1 from  _OA_HJXM)

              2.then (select isnull(max(number),0)+1 from  _OA_HJXM)

           else number

      end

 

 from inserted

end