我尝试过以下查询:在不到2家医院工作的医生是什么?但是结果不是我所期望的。
我有这些表:
CREATE TABLE Hospital (
hid INT PRIMARY KEY,
name VARCHAR(127) UNIQUE,
country VARCHAR(127),
area INT
);
CREATE TABLE Doctor (
ic INT PRIMARY KEY,
name VARCHAR(127),
date_of_birth INT,
);
CREATE TABLE Work (
hid INT,
ic INT,
since INT,
FOREIGN KEY (hid) REFERENCES Hospital (hid),
FOREIGN KEY (ic) REFERENCES Doctor (ic),
PRIMARY KEY (hid,ic)
);
我尝试了这个:
SELECT DISTINCT D.ic
FROM Doctor D, Work W
JOIN Hospital H ON (H.hid = W.hid)
WHERE D.bi = W.bi
GROUP BY (D.ic)
HAVING COUNT(H.hid) < 2
;
谢谢。
最佳答案
您需要使用LEFT JOIN
,而不必与表Hospital
联接
SELECT a.name
FROM Doctor a
LEFT JOIN `Work` b
ON a.ic = b.ic
GROUP BY a.name
HAVING COUNT(b.ic) < 2
关于sql - SQL具有计数和联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13540563/