PL/SQL - 如何从连接表中返回单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4829293/
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
PL/SQL - How to return single row from a joined table
提问by Ciaran Bruen
this might be quite simple I'm just not seeing the wood for the trees at the moment. In Oracle I'm selecting records from table A that joins to table B based on the primary key of table A. However table B can have multiple records matching the primary key of table A. This is causing my query to return duplicate rows from table A. Below is a cut down version of my query:
这可能很简单，我只是目前没有看到树木的木材。在 Oracle 中，我根据表 A 的主键从表 A 中选择连接到表 B 的记录。但是表 B 可以有多个记录与表 A 的主键匹配。这导致我的查询从表中返回重复的行A. 以下是我的查询的简化版本：
TableA TableB
_______ _________
1, Sec1 2, 11/01/2011
2, Sec2 2
3, Sec3 5, 10/01/2011
4, Sec4 6, 10/01/2011
Select A.SecID, A.SecName, B.DateSent from tableA A
inner join tableB B on A.SecID = B.SecID
This is returning 2 records for Sec2 - how can I get it to return only 1 record for Sec2? I've tried using distinct and unique but still get the same results.
这是为 Sec2 返回 2 条记录 - 我怎样才能让它只为 Sec2 返回 1 条记录？我试过使用不同的和独特的，但仍然得到相同的结果。
回答by Quassnoi
SELECT secid, secname
FROM tableA
WHERE secid IN
(
SELECT secid
FROM tableb
)
If you need a record from tableBas well:
如果您还需要以下记录tableB：
SELECT secid, secname, datesent
FROM (
SELECT a.secid, a.secname, b.datesent, ROW_NUMBER() OVER (PARTITION BY a.secid ORDER BY b.datesent DESC) AS rn
FROM tableA a
JOIN tableB b
ON b.secid = a.secid
)
WHERE rn = 1
ORDER BYclause controls which of the multiple records on bwill you get.
ORDER BY子句控制b您将获得多条记录中的哪一条。
回答by Vincent Malgrat
You can use a GROUP function to select only one row:
您可以使用 GROUP 函数仅选择一行：
SELECT A.SecID, A.SecName, max(B.DateSent) DateSent
FROM tableA A
JOIN tableB B on A.SecID = B.SecID
GROUP BY A.SecID, A.SecName
回答by Oleg Danu
SELECT DISTINCT a.secid, a.secname
FROM tableA a, tableB b
WHERE a.secid = b.secid;
回答by Vishnu Gupta
The solutions suggested are very good. There are cases when you may have take a bit different approach specially when one of the tables is very large compared to another one and absence of an index on foreign key column in table B.
建议的解决方案非常好。在某些情况下，您可能会采取一些不同的方法，特别是当其中一个表与另一个表相比非常大并且表 B 中的外键列上没有索引时。
