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 存储过程名