oracle 只需要从 INNER JOIN 返回一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4715791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need one row only returned from INNER JOIN
提问by adamdunne
I would like to return the first row only from an inner join. I have two tables:
我只想从内部联接返回第一行。我有两个表：
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
Rows in TABLE Y can have identical creation dates so I am first getting the MAX(creationdate) and then then MAX(id) from this set, for example:
表 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
)
)
)
)
This works when I call it on it's own but when I add it to an INNER JOIN I am getting a row for each matching row in table Y.
这在我自己调用它时有效，但是当我将它添加到 INNER JOIN 时，我为表 Y 中的每个匹配行获取一行。
What I want is the latest record by creationdate and id where xid = id from TABLE_X.
我想要的是来自 TABLE_X 的创建日期和 id 的最新记录，其中 xid = id。
回答by Fraz Sundal
Try this query
试试这个查询
select *,(
select top 1 creationdate from Table_Y
where from Table_Y.xId = m.id
order by Table_Y.CreationDate
)
from Table_X m
The sub query will pick the top 1 result which have max creationdate and the main query will pick all the records so you have your desired result
子查询将选择具有最大创建日期的前 1 个结果，主查询将选择所有记录，以便您获得所需的结果
回答by RichardTheKiwi
This should do it The complex subquery works out the max date for each Y.xid group, and from that, further works out the Max Y_ID (let this represent the key on table Y)
这应该可以 复杂的子查询计算出每个 Y.xid 组的最大日期，然后进一步计算出最大 Y_ID（让它代表表 Y 上的键）
SELECT X.*, Y.*
FROM TABLE_X X
INNER JOIN (
SELECT t1.xid, Max(t1.Y_id) MaxY_id
FROM
(SELECT t2.xid, MAX(t2.CREATIONDATE) MDate
FROM TABLE_Y t2
GROUP BY t2.xid) t
inner join TABLE_Y t1
on t.xid=t1.xid and t.MDate = t1.CREATIONDATE) MAXY
ON MAXY.xid = X.ID
INNER JOIN TABLE_Y Y
ON Y.Y_ID = MAXY.MAXY_ID
回答by fdreger
"when I add it to an inner join"? what inner join? with what inner join? The question is badly underspecified, but I think you need this (I only use views to be clear, you can easily just put them in braces and build one big query):
“当我将它添加到内部连接时”？什么内连接？用什么内连接？问题严重不足，但我认为您需要这个（我只使用视图来明确，您可以轻松地将它们放在大括号中并构建一个大查询）：
-- latest pairs of (id,creation) per xid
create view latest_id_per_xid as
select xid,max(id) id,max(creation) creation from table_y group by xid;
-- this view leaves only the rows from table_y that have the same (id,creation,idx)
-- as the newest rows identified in the former view (it's basically a semijoin)
-- you could also join on id alone
create view latest_whole_y as
select table_y.* from table_y natural join latest_id_per_xid;
-- now the answer is easy:
select * from table_x join latest_whole_y
I have no database at hand to check for small mistakes, but it should run fine. (caveat: the big assumption is that you never have a record with a newer id and older date)
我手头没有数据库来检查小错误，但它应该运行良好。（警告：最大的假设是您永远不会有新 ID 和旧日期的记录）
