1.左右连接的问题
这种 事情 我不确定 什么时候会用到(可能复杂的 储存过程会用到)
可能 一次 一张表和 3张以上的 表连接时,会用到
1-1 from a left join b on ......
就是说 以a 表为主 ,b表会把 空的 显示为 null
1-2 还可以在 连接中一次可以 连多次,以便于 把列显示多次
case when(
SELECT sum(
days*CASE WHEN PersonalLeaveType=0
THEN 100 ELSE 20 end 成立 则 100, 否则是 20
)
FROM
tb_personalleave WHERE emptId=emp.Id
AND to_char(PersonalLeaveDtm,''yyyy-MM'')=:dtm
)
嵌套了 两层
is NULL THEN 0
else ( ) end dddd--别名
................................
select id ,
case when () is null then 0 else () end name,
addr
from emp
.........................................................
2-2看看 sql servier的
create procedure proc_getPayDetail
(
@dtm varchar(20)
)
as
begin
select
@dtm,
emp.id,
when(
值为空 则 返回0 否则 返回 本身
select sum(days*150 ) from tb_Absence
where emptId=emp.id and CONVERT(varchar(100),tb_Absence.absenceDtm, 102)=@dtm)
is null
then 0
else(
select sum(days*150 ) from tb_Absence
where emptId=emp.id
and CONVERT(varchar(100),tb_Absence.absenceDtm, 102)=@dtm)
end absencepay
from tb_emp emp
end
public void updateEmp(EmployeeVo emp) throws Exception{
Connection conn = this.openConnection();
CallableStatement cst = conn.prepareCall("{call sp_updateemp(?,?)}");
cst.setInt(1, emp.getEmpId());
cst.setString(2, emp.getEmpName());
//执行
cst.execute();
//如果返回表结构
// ResultSet rs = cst.getResultSet();....
}
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
2-3 mysql的写法
create PROCEDURE proc_getPayDetail(IN dtm VARCHAR(20))
BEGIN
SELECT emp.empId,emp.ename,emp.basepay,
CASE
WHEN (SELECT sum(hours * 20) FROM tb_overtime
WHERE emptId=emp.Id
AND DATE_FORMAT(overtimeDtm,'%Y-%m')=dtm)
is NULL THEN 0
ELSE (SELECT hours*20
FROM tb_overtime
WHERE emptId=emp.Id AND
DATE_FORMAT(overtimeDtm,'%Y-%m')=dtm)
END overtimepay,
CASE
WHEN (SELECT sum(
days*(CASE WHEN PersonalLeaveType=0
THEN 100 ELSE 20 END) 成立 则 100, 否则是 20
)
FROM
tb_personalleave WHERE emptId=emp.Id
AND DATE_FORMAT(PersonalLeaveDtm,'%Y-%m')=dtm)
嵌套了 两层
is NULL THEN 0
ELSE (SELECT sum(days*(CASE WHEN PersonalLeaveType=0 THEN 100 ELSE 20 END))
FROM tb_personalleave WHERE emptId=emp.Id AND DATE_FORMAT(PersonalLeaveDtm,'%Y-%m')=dtm)
END personalleavepay,
CALL proc_getPayDetail('2016-05');