oracle 获取连接的第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1848596/
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
get the first row of a join
提问by Andrea Girardi
I've a query like this:
我有一个这样的查询：
select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id
I need to get the first row that satisfy the second condition.
我需要得到满足第二个条件的第一行。
Any idea?
任何的想法？
回答by Bertrand Marron
SELECT t1.id, t1.sample, t2.id FROM table t1 JOIN TABLE t2
ON t1.sample = t2.sample AND t2.id > t1.id WHERE ROWNUM = 1
回答by Ryan Ahearn
All rows returned will satisfy both conditions, so you don't have to do anything special to make sure that the second condition is satisfied.
返回的所有行都将满足这两个条件，因此您无需执行任何特殊操作即可确保满足第二个条件。
If you want to limit the returned results size to 1, append WHERE ROWNUM = 1to the query if it will be run on Oracle.
如果要将返回的结果大小限制为 1，请附加WHERE ROWNUM = 1到查询是否将在 Oracle 上运行。
回答by Kico Lobo
Well.
好。
You could try this: if you're working with SQL Server, add a top 1
你可以试试这个：如果你使用 SQL Server，添加一个前 1
if you're working with MySQL, add a limit 1
如果您使用 MySQL，请添加限制 1
it will only return the first row.
它只会返回第一行。
To ensure, you can add an order clause too.
为了确保，您也可以添加一个 order 子句。
回答by ctford
Oracle uses something called "ROWNUM". Limiting the number of results is annoyingly inconsistent across DBMSs.
Oracle 使用称为“ROWNUM”的东西。限制结果的数量在 DBMS 之间是不一致的。
SELECT t1.id, t1.sample, t2.id
FROM table t1 join table t2
ON t1.sample = t2.sample and t2.id > t1.id
WHERE ROWNUM <= 1
回答by Dinesh Bhat
If you want the least t2.id that satisfies the second condition then
如果你想要满足第二个条件的最少 t2.id 那么
select * from
(select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id ) where rownum =1
select * from
(select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id ) where rownum =1
If you want the greatest t2.id that satisfies the second condition then
如果你想要满足第二个条件的最大 t2.id 那么
select * from
(select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id desc) where rownum =1
select * from
(select t1.id, t1.sample, t2.id from table t1 join table t2
on t1.sample = t2.sample and t2.id > t1.id order by t2.id desc) where rownum =1
