假设我有三张桌子：
离开类型：
id | name
1 | maternity leave
2 | Medical leave
3 | Casual Leave
4 | Sick Leave
5 | honeymoon Leave
雇员
id | name
4 | Employee 1
7 | Employee 2
员工离职
id | employee_id | year | leave_type_id | val
1 | 4 | 2015 | 1 | 9
2 | 4 | 2015 | 2 | 5
3 | 7 | 2015 | 1 | 10
4 | 4 | 2015 | 3 | 4
这里，
employee_leave_blance.leave_type_id = leave_type.id现在我想得到所有员工的所有
Leave Type值。让我举个例子：我想得到
employee_id = 4, 7的输出employee_id | name | val
4 | maternity leave | 9
4 | Medical leave | 5
4 | Casual Leave | 4
4 | Sick Leave | 0
4 | honeymoon Leave | 0
7 | maternity leave | 10
7 | Medical leave | 0
7 | Casual Leave | 0
7 | Sick Leave | 0
7 | honeymoon Leave | 0
我的问题
SELECT
lt.name, ifnull(el.val,0) as val , el.employee_id
FROM leave_type AS lt
LEFT JOIN employee_leave_balance AS el ON el.leave_type_id = lt.id
AND
el.year = YEAR(CURDATE()) GROUP BY el.employee_id
最佳答案
你可以这样做：
SELECT employee_id,leave_name,IFNULL(val,0) as val
FROM
(SELECT e.id as employee_id,l.id as lid,l.name as leave_name
FROM employee e CROSS JOIN leave_type l) as T1 LEFT JOIN
(SELECT val,employee_id as eid,leave_type_id as lid
FROM employee_leave_blance elb) as T2 ON T1.employee_id=T2.eid AND T1.lid=T2.lid
ORDER BY employee_id asc,val desc
结果：
employee_id leave_name val
--------------------------------
4 maternity leave 9
4 Medical leave 5
4 Casual Leave 4
4 Sick Leave 0
4 honeymoon Leave 0
7 maternity leave 10
7 Casual Leave 0
7 honeymoon Leave 0
7 Sick Leave 0
7 Medical leave 0
样本结果SQL Fiddle。
关于php - mysql左边连接三个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30725340/