这是我的表格:
从用户中选择 *
USERID NAME STATUS
1 AAA Member
2 BBB Member
3 CCC Supervisor
4 DDD Member
5 EEE Member
从出席中选择 *
NO DATE SUPERVISOR MEMBER
1 2019-12-11 3 1
2 2019-12-11 3 2
3 2019-12-11 3 4
4 2019-12-11 3 5
从详细信息中选择*
NO USERID ATTENDANCE REASON
1 1 0 SICK
2 2 1 -
3 4 1 -
4 5 1 -
我想要的结果:
USERID DATE NAME REASON SUPERVISOR
1 2019-12-11 AAA SICK CCC
2 2019-12-11 BBB - CCC
3 2019-12-11 DDD - CCC
4 2019-12-11 EEE - CCC
我尝试过的 SQL 调用主管的用户 ID,而不是主管的名称。
SELECT d.userid, a.date, u.name, d.reason, a.supervisor FROM attendance a INNER JOIN detail d on a.userid = d.userid INNER JOIN user u on d.userid = u.userid WHERE d.attendance=0
我需要有关 sql 的帮助来调用主管的姓名而不是用户 ID。
谢谢..
您必须连接用户表两次,因为您要匹配成员名称和主管名称。我也用过LEFT JOIN
因为有时原因没有值,但您仍然想从其他表中提取记录。
SELECT u1.userid, a.date, u1.name, d.reason, u2.name AS supervisor
FROM user u1
LEFT JOIN attendance a ON u1.userid = a.member
LEFT JOIN detail d ON u1.userid = d.userid
LEFT JOIN user u2 ON a.supervisor = u2.userid
WHERE d.attendance = 0
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)