初识SQL

SQL加判断

select B.FName 员工,FOverDay 加班天数,FAbsenDay 旷工天数,FLateDay 迟到天数,FWithHold 扣款,FSubSidy 补贴
        ,case when A.FLateDay%3=0 then A.FLateDay/3*100 else floor(A.FLateDay/3)*100+20*A.FLateDay%3 end  考勤工资
 from STKD_T_StaffMonAttEntry as A
       join STKD_T_WorkerInfoEntry as B ON (A.FWoker=B.FID)

查询函数值

select FLOOR(23.99)

临时表

select A.FName 员工,B.FName 介绍人,
datediff(month,A.FInDate,getdate()) 在职月份  
into #Temp
from STKD_T_WorkerInfoEntry as A
left JOIN STKD_T_WorkerInfoEntry as B on A.FReferees = B.FID
where datediff(month,A.FInDate,getdate()) = 3

select 介绍人,count(员工)
from #Temp
group BY 介绍人

drop TABLE #Temp

不为空是 is not null

select A.FName,
    CASE WHEN A.FReferees is NOT NULL THEN (CASE WHEN datediff(month,A.FInDate,getdate())=0 THEN A.FReferees ELSE NULL END) ELSE NULL END 到期介绍人
from STKD_T_WorkerInfoEntry as A

判断为空

//如果是null的话
select * from 表名 where 字段名 is null
//如果是空字符的话
select * from 表名 where 字段名=''

触发器判断数据已经存在

CREATE trigger STKD_TR_MacInser
on STKD_T_MacConfig
for insert
as
IF exists (select s.FMacName from STKD_T_MacConfig s where s.FMacName in (select i.FMacName from inserted AS i))
    begin 
            RAISERROR('机台已经存在', 16, 1)
    end;

有效利用like
当string为空的时候就是不过滤

where a like '%'+string+'%'

error:列名 ‘年月1’ 无效
当出现列名无效是，而表确实有该列时，查看是否是临时表未删除重新建立。存储过程临时表不删除不会报错。
利用存储过程插入到表中

create table #tb(a int)
insert into #tb(a) exec 存储过程名