SELECT PRH.prid, PRH.CreationDate, PRH.ClosedDate, PRA.creationdate
FROM PRHeader PRH
left join PRAction PRA on PRH.PrId = PRA.PrId
WHERE
PRA.CreationDate =
(select min(CreationDate ) from PRAction pa WHERE PRH.prid=pa.prid
and pa.ActionTypeId<>4)
好吧,我不知道如何解释这一点...但是我有一个带有对象的表 PRHeaderPRHeader 中的每个对象在 PRAction 中都有 0 多个相关操作。上面的代码为具有相关操作类型的每个对象获取最早的操作,因为我想要该对象的时间戳。问题是某些对象没有任何符合此条件的操作,然后我仍然希望返回该对象,但以 null 作为操作时间。显然,我只想要每个对象一次。有什么建议吗?
非常感谢戈登·利诺夫!效果很好!
您可以将where
条件移动到 from
子句中,您可以在其中left outer join
。 一种方法是将条件放在on
子句中:
SELECT PRH.prid, PRH.CreationDate, PRH.ClosedDate, PRA.creationdate
FROM PRHeader PRH left join
PRAction PRA
on PRH.PrId = PRA.PrId and
PRA.CreationDate = (select min(CreationDate)
from PRAction pa
WHERE PRH.prid = pa.prid and pa.ActionTypeId<>4
);
另一种方法是将其作为join
条件:
select PRH.prid, PRH.CreationDate, PRH.ClosedDate, pa.creationdate
from PRHeader PRH left join
(select prid, min(CreationDate)
from PRAction pa
where pra.ActionTypeId <> 4
group by prid
) pa
on PRH.prid = pa.prid;
最后,如果您不想执行聚合,则可以将字段计算移动到 select
子句:
select PRH.prid, PRH.CreationDate, PRH.ClosedDate,
(select min(CreationDate)
from PRAction pa
where PRH.prid = pa.prid and pa.ActionTypeId <> 4
) as CreationDate
from PRHeader PRH;
虽然这看起来像一个聚合,但引擎应该能够直接在PRAction(prid, CreationDate)
上使用索引。 您可以通过创建子查询来使其更加明显(使用 MySQL 语法):
(select CreationDate
from PRAction pa
where PRH.prid = pa.prid and pa.ActionTypeId <> 4
order by CreationDate
limit 1
) as CreationDate
您可以在问题中尝试左联接,但联接到嵌套查询。我认为它会是这样的:
SELECT PRH.prid, PRH.CreationDate, PRH.ClosedDate, PRA.creationdate
FROM PRHeader PRH
left outer join (
SELECT *
FROM PRAction pa
WHERE pa.CreationDate = (
SELECT min(pa2.CreationDate)
FROM PRAction pa2
WHERE pa.prid=pa2.prid
AND pa2.ActionTypeId<>4)
) PRA on PRH.PrId = PRA.PrId