php中左联接两张表怎么做,php – mysql左连接两个表

假设我有2个表：

leave_type

id name

1 maternity leave

2 Medical leave

3 Casual Leave

4 Sick Leave

5 honeymoon Leave

employee_leave_blance

id employee_id year leave_type_id val

1 4 2015 1 9

2 4 2015 2 5

3 8 2015 1 10

4 4 2015 3 4

这里employee_leave_blance.leave_type_id = leave_type.id

现在我想获得employee_id = 4的所有Leave Type值

在Employ_id = 4的employee_leave_blance表中,只有3个leave_type条目,但在leave_type表中有5个.

我想为一名员工获得5个条目.如果在employee_leave_blance中找不到任何条目,则它应返回0.

让我举一个例子：我想得到employee_id = 4的输出

employee_id name val

4 maternity leave 9

4 Medical leave 5

4 Casual Leave 4

4 Sick Leave 0

4 honeymoon Leave 0

为此,我尝试了以下查询：

select

el.employee_id, lt.name, el.val from leave_type as lt

left join employee_leave_blance as el on el.leave_type_id = lt.id

where

el.year = YEAR(CURDATE()) and el.employee_id = 4

然而它返回以下结果：

employee_id name val

4 maternity leave 9

4 Medical leave 5

4 Casual Leave 4

现在我如何获得员工的所有leave_type的值？

解决方法:

不确定这是否是您所需要的,请尝试：

select ifnull(el.employee_id,4) as employee_id,

lt.name,ifnull(el.val,0) as val

from leave_type as lt

left join (select year,employee_id,val,leave_type_id

from employee_leave_balance) as el

on el.leave_type_id = lt.id

and el.year = year(curdate())

and el.employee_id = 4

实际上,您的查询将适用于一些修改.只需删除where子句,因为你已经连接了表并使用ifnull函数为空值返回0.这是您的查询,但有一些改进：

select

ifnull(el.employee_id,4) as employee_id, lt.name, ifnull(el.val,0) as val

from leave_type as lt

left join employee_leave_balance as el on el.leave_type_id = lt.id

and

el.year = YEAR(CURDATE()) and el.employee_id = 4

标签：php,mysql

来源： https://codeday.me/bug/20190708/1400342.html