php – mysql左连接两个表

发布时间：2020-12-13 15:56:21 所属栏目：PHP教程来源：网络整理

导读：假设我有2个表： leave_type id name1 maternity leave2 Medical leave3 Casual Leave4 Sick Leave5 honeymoon Leave employee_leave_blance id employee_id year leave_type_id val1 4 2015 1 92 4 2015 2 53 8 2015 1 104 4 2015 3 4 这里employee_leave_b

假设我有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,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,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!