如何在MYSQL中获取主管的姓名而不是UserID + INNER JOIN [英] How to Get the Supervisor's Name instead of the UserID + INNER JOIN in MYSQL

问题描述

这是我的表格:

<块引用>

从用户中选择*

用户ID名称状态1个AAA会员2 BBB 会员3 CCC主管4 DDD会员5 EEE会员

<块引用>

从出席中选择*

没有日期监督成员1 2019-12-11 3 12 2019-12-11 3 23 2019-12-11 3 44 2019-12-11 3 5

<块引用>

从详情中选择*

无用户名出席原因1 1 0 生病2 2 1 -3 4 1 -4 5 1 -

我想要的结果:

USERID DATE NAME REASON SUPERVISOR1 2019-12-11 AAA SICK CCC2 2019-12-11 BBB - CCC3 2019-12-11 DDD - CCC4 2019-12-11 EEE - CCC

我尝试过的 SQL 调用主管的用户 ID，而不是主管的姓名.

SELECT d.userid, a.date, u.name, d.reason, a.supervisor FROM参加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来自用户 u1LEFT JOIN 出席人数 a ON u1.userid = a.memberLEFT JOIN detail d ON u1.userid = d.userid左加入用户 u2 ON a.supervisor = u2.useridWHERE d.出勤率 = 0

Here are my tables :

select * from user

USERID  NAME    STATUS
1       AAA     Member
2       BBB     Member
3       CCC     Supervisor
4       DDD     Member
5       EEE     Member

select * from attendance

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

select * from detail

NO  USERID  ATTENDANCE  REASON
1   1       0           SICK
2   2       1           -
3   4       1           -
4   5       1           -

The result I want :

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

The SQL I've tried calls the supervisor's userid instead of the name of the supervisor.

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

I need help with the sql to call the supervisor's name not the userid. Thanks..

解决方案

You have to join the user table twice because you are matching it for both the member name and the supervisor name. I also used LEFT JOIN because sometimes the reason does not have a value but you still want to pull the records from the other tables.

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

这篇关于如何在MYSQL中获取主管的姓名而不是UserID + INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！