SQL 如何加入两个 SELECT 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9047538/
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
How can I join two SELECT statements?
提问by user1099310
select *
From
(
select *
from order
order by creationtime desc
)
where rownum=1 and creationtime='12-feb-2010';
and
和
select *
from
(
select *
from
order by rate desc
)
where rownum<=2 and creationtim='12-dec-2011';
I want to join these two SELECTqueries, using JOIN. Both SELECTqueries query from same table. I do not want use UNION.
我想加入这两个SELECT查询，使用JOIN. 两个SELECT查询都从同一个表中查询。我不想使用UNION.
How can I achieve this?
我怎样才能做到这一点？
回答by Michael Kingsmill
It is hard to tell from your question what all should be in the "ON" clause below since you didn't indicate the primary key, but this should give you the idea of what you need to do.
由于您没有指明主键，因此很难从您的问题中看出下面“ON”子句中的所有内容应该是什么，但这应该让您知道需要做什么。
select * From
(select * from order order by creationtime desc) A
INNER JOIN (select * from order by rate desc) B
ON A.rownum = B.rownum
where A.rownum=1 and A.creationtime='12-feb-2010'
AND B.rownum<=2 and B.creationtim='12-dec-2011'
回答by Adam Musch
Use the row_number() analytic function to get the first row for each value.
使用 row_number() 分析函数获取每个值的第一行。
select [list of columns]
from (select o.*, row_number() over (partition by creationtime)
from order o
where creatontime in ('12-DEC-2011', '12-FEB-2010')
)
where rn = 1;
