查看: 1284|回复: 6
|
3 个 table join 需要show 一个table全部record,其他两个table有record的就show
[复制链接]
|
|
这里有三个table,employee,quota,leave,我需要全部employee的record,然后join quota table,要知道他的quota多少,然后leave table里面的record有拿leave的才有record,没有拿leave的就一条record都没有的,但是我希望出来的result是全部employee的名字然后show quota,和他全部拿过的sick leave,和total 起来拿了多少天的sick leave。- employee
- ----------------
- emp ID|name
- ----------------
- 1 |emp 1
- 2 |emp 2
- 3 |emp 3
- 4 |emp 4
- ----------------
- quota
- -----------------------
- emp ID|leave type|quota
- -----------------------
- 1 |annual |5
- 1 |sick |14
- 2 |annual |4
- 2 |sick |14
- 3 |annual |3
- 3 |sick |14
- 4 |annual |2
- 4 |sick |14
- ----------------------
- leave
- ---------------------------------
- emp ID|leave type |date |day
- ---------------------------------
- 1 |annual |22-5-2013|0.5
- 2 |sick |22-5-2013|1.0
- 1 |sick |24-5-2013|1.0
- 1 |sick |25-5-2013|1.0
- ---------------------------------
- My expected result:
- --------------------------------------------
- emp ID|name |leave type|quota|date |day
- --------------------------------------------
- 1 |emp 1|sick |14 |24-5-2013|2.0
- 2 |emp 2|sick |14 |22-5-2013|1.0
- 3 |emp 3|sick |14 |null |null
- 4 |emp 4|sick |14 |null |null
- --------------------------------------------
复制代码 请各位大大帮帮忙,已经不知道怎么写了。
本帖最后由 星之梦 于 25-7-2013 03:35 PM 编辑
|
|
|
|
|
|
|
|
发表于 26-7-2013 10:08 PM
|
显示全部楼层
沙发 LEFT OUTER JOIN 可以帮到你 ![](static/image/smiley/default/icon_redface.gif) |
|
|
|
|
|
|
|
发表于 27-7-2013 07:35 PM
|
显示全部楼层
应该类似酱
- select t0.empId, t0.empName, t1.leaveType, t1.quota, sum(t2.day) as day
- from employee t0
- left join quota t1 on t0.empId = t1.empId
- left join leave t2 on t0.empId = t2.empId and t1.leaveType = t2.leaveType
- group by t0.empId, t0.empName, t1.leaveType, t1.quota
复制代码 |
|
|
|
|
|
|
|
发表于 28-7-2013 12:12 AM
|
显示全部楼层
楼上的基本正确,我只是补充。![](static/image/smiley/default/shy.gif) - select e.employee_id, e.employee_name, q.leave_type, q.quota, sum(l.day) as day from tbl_employee e left join tbl_quota q on e.employee_id = q.employee_id left join tbl_leave l on e.employee_id = l.employee_id and q.leave_type = l.leave_type where q.leave_type = 'sick' group by e.employee_id, q.leave_type
复制代码 |
|
|
|
|
|
|
|
发表于 28-7-2013 01:22 AM
|
显示全部楼层
hui_wooi 发表于 28-7-2013 12:12 AM ![](static/image/common/back.gif)
楼上的基本正确,我只是补充。
没注意到只是要 sick 而已。。。 failed 了 ![](static/image/smiley/default/mad.gif)
|
|
|
|
|
|
|
|
发表于 19-8-2013 03:33 PM
|
显示全部楼层
|
|
|
|
|
|
|
![](static/image/common/ico_lz.png)
楼主 |
发表于 29-8-2013 10:43 AM
|
显示全部楼层
嗯,谢谢各位大大,已经有sql code成功了。![](static/image/smiley/default/victory.gif) |
|
|
|
|
|
|
| |
本周最热论坛帖子
|