我想仅从内连接返回第一行.我有两张桌子：
TABLE_X | TABLE_Y id | id creationdate xid 1 | 1 01/01/2011 1 2 | 2 01/01/2011 1 3 | 3 31/12/2010 2 4 | 4 28/12/2010 3
表Y中的行可以具有相同的创建日期,因此我首先获得MAX(creationdate),然后从该集合获得MAX(id),例如：
SELECT a.id,c.id,d.id,e.id,d.CREATIONDATE,a.REFNUMBER,a.DATECREATED,a.DESCRIPTION,e.CATEGORYCODE,e.OUTSTANDINGAM_MONAMT,e.PREVPAIDAMOUN_MONAMT,e.TOTALINCURRED_MONAMT,e.LOSSFROMDATE,FROM
TABLE_A a
INNER JOIN TABLE_B b ON (b.id = a.id)
INNER JOIN TABLE_C c ON (c.id = b.id)
INNER JOIN TABLE_D d ON
(
c.i =
(
select
d.id
FROM TABLE_D
WHERE TABLE_D.id = c.id
AND TABLE_D.id =
(
select
max(id)
from TABLE_D t1
where c_id = c.id
and CREATIONDATE =
(
select
max(CREATIONDATE)
from TABLE_D t2
where t2.c_id = t1.c_id
)
)
)
)
INNER JOIN TABLE_E e ON
(
d.i =
(
select
e.d_id
from TABLE_E
where d_id = d.id
AND id =
(
select
max(id)
from e t1
where e.d_id = d.id
and CREATIONDATE =
(
select
max(CREATIONDATE)
from TABLE_E t2
where t2.d_id = t1.d_id
)
)
)
)
当我自己调用它时,这可以工作,但是当我将它添加到INNER JOIN时,我为表Y中的每个匹配行获取一行.
我想要的是creationdate和id的最新记录,其中来自TABLE_X的xid = id.
