SQL 查询返回错误结果

Question

对于大学作业，我们必须根据我们得到的问题编写一堆 SQL 查询，最后一个问题指出:

`Write a query that will indicate which employee has been booked off for the mostnumber of days by doctor ‘D0001’. Display the employee name and surname,doctor name, and the number of days

我的问题是，即使我说出我想要选择的值，它也会返回错误的医生姓名，我无法理解为什么会这样。查询中的其他值按应有的方式显示，唯一的问题是医生 ID 中的医生姓名显示不正确

表格

CREATE TABLE DOCTORS(
    DOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEY,
    DOCTOR_NAME VARCHAR(30) NOT NULL
)

我们必须添加的值(value)观

INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0001', 'Thabo Ntlali')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0002', 'Deon Coetzee')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0003', 'Kwezi Mbete')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0004', 'Trevor January')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0005', 'Julia Robin')

我写的查询

SELECT TOP(1) emp.EMPLOYEE_NAME, emp.EMPLOYEE_SURNAME, d.DOCTOR_NAME, MAX(e.NUMBER_OF_DAYS) AS "NUMBER_OF_DAYS"
FROM DOCTORS AS D, EMPLOYEES emp 
JOIN EMPLOYEE_SICKLEAVE e
ON emp.EMPLOYEE_ID = e.EMPLOYEE_ID
WHERE e.DOCTOR_ID = 'D0001' 
GROUP BY emp.EMPLOYEE_NAME, emp.EMPLOYEE_SURNAME, d.DOCTOR_NAME
ORDER BY emp.EMPLOYEE_NAME, EMP.EMPLOYEE_SURNAME

Answer 1

评论中非常重要的一点

Tip of today: Switch to modern, explicit JOIN syntax everywhere. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.

~ jarlh

那么让我们加入你的连接:

...
FROM DOCTORS AS D, EMPLOYEES emp 
JOIN EMPLOYEE_SICKLEAVE e
ON emp.EMPLOYEE_ID = e.EMPLOYEE_ID
...

这隐藏了一个事实，即有 3 个表，但只有一个 ON 子句!

这可能是您遇到问题的原因。

所以让我们[尽可能]重写它们:

...
FROM   DOCTORS
 INNER
  JOIN EMPLOYEES
    ON EMPLOYEES.<SOMETHING> = DOCTORS.<SOMETHING>
 INNER
  JOIN EMPLOYEE_SICKLEAVE
    ON EMPLOYEE_SICKLEAVE.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
...