SQL 查询 - 计数 - 最大值

Question

我无法提出一个问题的查询。我有三张 table

CREATE TABLE institute (
    iid INT PRIMARY KEY,
    sign VARCHAR(127) UNIQUE, 
    city VARCHAR(127) NOT NULL,
    area INT CHECK (area>0));

CREATE TABLE desease (
    did INT PRIMARY KEY,
    name VARCHAR(127) UNIQUE,
    level INT CHECK (level>0));

CREATE TABLE studies (
    did INT,
    iid INT,
    FOREIGN KEY (did) REFERENCES desease (did),
    FOREIGN KEY (iid) REFERENCES institute (iid),
    PRIMARY KEY (iid,did));

我的问题是:里斯本(里斯本是 city 来自 institute)的最多机构的疾病名称是什么？这是我想出的，但它没有给我正确的答案。

SELECT DISTINCT D.name, MAX(I.iid)
  FROM desease D, studies S
  JOIN institute I ON (S.iid = I.iid)
 WHERE I.city = 'Lisboa' AND D.did = S.did
 GROUP BY D.nome
HAVING COUNT(I.iid) = MAX(I.city)

举个例子:假设有 5 个机构都带有 city = 'Lisbon' 并且 iid A,B,C,D,E respectevely(仅用于演示目的，我知道类型是 INT)； 5 种疾病的名称分别为 Z、X、N、V、M。

现在假设疾病 Z、X 和 M 由研究所 A、B、C(以任何顺序)研究，疾病 N 由 D(1 个研究所)研究，疾病 V 由 E(只有一个)研究.所以里斯本研究所研究的疾病的最大数量是 3(A、B 和 C 都研究 3 种疾病)所以表格看起来像这样

Z - 3
X - 3
M - 3

编辑:我设法找到了一种方法来做到这一点。这是我想出的查询

SELECT DISTINCT D.name, COUNT(*) AS C
FROM desease D, studies E, institute I
WHERE I.iid = E.iid AND D.did = E.did AND I.city = "Lisboa"
GROUP BY D.name
HAVING C >= ALL (
SELECT COUNT(*)
FROM desease D, studies E, institute I
WHERE I.iid = E.iid AND D.did = E.did AND I.cidade = "Lisboa"
GROUP BY D.name

);

Answer 1

我不太了解结构/问题，但我确实看到您在混合联接并且有一个交叉联接，这会增加记录的数量。

SELECT DISTINCT D.name, MAX(I.iid)
FROM desease D
INNER JOIN  studies S ON D.iid=S.Did
INNER JOIN  institute I ON (S.iid = I.iid)
WHERE I.city = 'Lisboa' AND D.did = S.did
GROUP BY D.nome
HAVING COUNT(I.iid) = MAX(I.city)