gpt4 book ai didi

SQL 查询返回错误结果

转载 作者:行者123 更新时间:2023-12-01 23:12:19 25 4
gpt4 key购买 nike

对于大学作业,我们必须根据我们得到的问题编写一堆 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

最佳答案

评论中非常重要的一点

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
...

关于SQL 查询返回错误结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/69657650/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com