我需要帮助过滤查询返回的结果行。我有一个疾病表,列有百分比、病情和日期。
SELECT I.percent, I.illness, I.date
FROM Illnesses I
INNER JOIN Person P on I.person_id = P.id // an inner join I use in where clause
WHERE P.id = someId // 13 let's say
所以在我的情况下,对于id为13的人,查询将返回
51, someIllness, 2015-12-07
43,someOtherIllness, 2015-12-08
null, anotherIllness, 2015-12-08
我需要选择日期最新的行,如果有多行具有该日期,则需要选择最高百分比的行。我在where子句中添加了以下代码
AND I.date = (SELECT max(I2.date)
FROM Illnesess I2
INNER JOIN Person P2 on I2.person_id = P2.id
WHERE P2.id = P.id)
AND I.percent = (SELECT max(I2.percent)
FROM Illnesess I2
INNER JOIN Person P2 on I2.person_id = P2.id
WHERE P2.id = P.id AND I2.date = I.date)
这将返回43,someOtherIllness,2015-12-08,并且至少我认为会正常工作。如果最近日期的百分比为null,则我需要覆盖一个异常我需要使用具有百分比的前一日期行。
51, someIllness, 2015-12-07
null,someOtherIllness, 2015-12-08
null, anotherIllness, 2015-12-08
51,someDisease,2015-12-07将是我需要的结果
如果有人帮忙,我将不胜感激。谢谢
如果您要从一开始就删除所有没有百分比的疾病?
SELECT I.percent, I.illness, I.date
FROM Illnesses I
INNER JOIN Person P on I.person_id = P.id // an inner join I use in where clause
WHERE P.id = someId
AND I.percent IS NULL
你可以试试这个
SELECT I.percent, I.illness, I.date
FROM Illnesses I
INNER JOIN Person P on I.person_id = P.id
WHERE I.percent IS NOT NULL
ORDER BY I.date, I.percent DESC
LIMIT 1
如果您想先按最高百分比排序,您可以尝试通过交换订单中的日期和百分比位置