我有一个问题,即使在这里和sqlite.org重新注册后,我也无法解决
所以,我有这些表:
CREATE TABLE MEDICO(
idMedico INTEGER PRIMARY KEY AUTOINCREMENT,
nome VARCHAR(50) NOT NULL,
morada VARCHAR(50) NOT NULL,
telefone VARCHAR(9) NOT NULL
);
CREATE TABLE PRESCRICAO(
idPrescricao INTEGER PRIMARY KEY AUTOINCREMENT,
idConsulta INTEGER,
idMedico INTEGER NOT NULL,
nrOperacional INTEGER NOT NULL,
FOREIGN KEY(idConsulta) REFERENCES CONSULTA(idConsulta),
FOREIGN KEY(idMedico) REFERENCES MEDICO(idMedico),
FOREIGN KEY(nrOperacional) REFERENCES UTENTE(nrOperacional)
);
CREATE TABLE PRESCRICAO_MEDICAMENTO(
idPrescricao INTEGER ,
idMedicamento INTEGER,
nrEmbalagens INTEGER NOT NULL,
FOREIGN KEY(idPrescricao) REFERENCES PRESCRICAO(idPrescricao),
FOREIGN KEY(idMedicamento) REFERENCES MEDICAMENTO(idMedicamento),
PRIMARY key(idPrescricao, idMedicamento)
);
我想要MEDICO最常用的idMedicamento可以说idMedico = 7,
直到这里,一切都很好,我正在做:
SELECT idmedicamento, MAX(total) as maximum
FROM (SELECT idMedicamento, COUNT(idMedicamento) AS total
FROM PRESCRICAO_MEDICAMENTO
WHERE PRESCRICAO_MEDICAMENTO.idPrescricao IN (
SELECT idPrescricao FROM PRESCRICAO
WHERE PRESCRICAO.idmedico= 7
)
GROUP BY idMedicamento);
我得到:
IDmedicamento:3
最多:5
这是我想要的,这是正确的。
但是当我这样做时:
SELECT idMedicamento
FROM (SELECT idMedicamento, MAX(total) as maximum
FROM (SELECT idMedicamento, COUNT(idMedicamento) AS total
FROM PRESCRICAO_MEDICAMENTO
WHERE PRESCRICAO_MEDICAMENTO.idPrescricao IN (
SELECT idPrescricao FROM PRESCRICAO
WHERE PRESCRICAO.idmedico= 7
)
GROUP BY idMedicamento));
我所得到的是MEDICO最后使用的idMedicamento,在这种情况下,是idMedicamento = 5的MEDICAMENTO。
知道我在做什么错吗?真的无法弄清楚。
谢谢
最佳答案
在许多情况下,从记录中获取最大值的其他列的最简单方法是使用ORDER BY
/ LIMIT 1
:
SELECT idMedicamento
FROM PRESCRICAO_MEDICAMENTO
WHERE idPrescricao IN (SELECT idPrescricao
FROM PRESCRICAO
WHERE idmedico = 7)
GROUP BY idMedicamento
ORDER BY COUNT(*) DESC
LIMIT 1