SQL 如何只选择满足条件的第一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16034226/
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 to select only first rows that satisfies conditions?
提问by Jorge Vega Sánchez
I'm doing a join between two tables and adding a condition want to obtain only the first row that satisfie the join condition and the "extern" condition too.
我正在两个表之间进行连接并添加一个条件，只想获得满足连接条件和“extern”条件的第一行。
This query for example:
此查询例如：
select * from PRMPROFILE p, user v
where
p.id = v.profile
and p.language = 0
and v.userid like '%TEST%';
First of all, i want to know how to group the result of this inner join using the profile (v.profile or p.id). After that how to show only the first appearence for each group.
首先，我想知道如何使用配置文件 (v.profile or p.id) 对这个内部连接的结果进行分组。之后如何仅显示每个组的第一次出现。
Thanks in advance.
提前致谢。
回答by Alex Poole
You can use an analytic query for this:
您可以为此使用分析查询：
select *
from (
select p.*, v.*,
row_number() over (partition by p.id order by v.userid) as rn
from prmprofile p
join user v on v.profile = p.id
where p.language = 0
and v.userid like '%TEST%'
)
where rn = 1;
The inner query gets all the data (but using *isn't ideal), and adds an extra column that assigns a row number sequence across each p.idvalue. They have to be ordered by something, and you haven't said what makes a particular row 'first', so I've guessed as user ID - you can of course change that to something more appropriate, that will give consistent results when the query is rerun. (You can look at rankand dense_rankfor alternative methods to pick the 'first' row).
内部查询获取所有数据（但使用*并不理想），并添加一个额外的列，为每个p.id值分配一个行号序列。它们必须按某种顺序排序，并且您还没有说是什么使特定行成为“第一”，所以我猜测是用户 ID - 您当然可以将其更改为更合适的内容，这将在以下情况下提供一致的结果重新运行查询。（您可以查看rank和dense_rank寻找选择“第一”行的替代方法）。
The outer query just restricts the result set to those where the extra column has the value 1, which will give you one row for every p.id.
外部查询只是将结果集限制为那些额外列具有值的结果集1，这将为每个p.id.
Another approach would be to use a subquery to identify the 'first' row and join to that, but it isn't clear what the criteria would be and if it would be selective enough.
另一种方法是使用子查询来识别“第一”行并加入其中，但不清楚标准是什么以及它是否具有足够的选择性。
回答by TechDo
Please try:
请尝试：
select * from(
select *,
row_number() over (partition by v.userid order by v.userid) RNum
from PRMPROFILE p, user v
where
p.id = v.profile
and p.language = 0
and v.userid like '%TEST%'
)x
where RNum=1;
回答by FrostNovaZzz
You can use LIMIT keyword.
您可以使用 LIMIT 关键字。
select * from PRMPROFILE p, user v
where
p.id = v.profile
and p.language = 0
and v.userid like '%TEST%'
limit 1
回答by user10772902
select * from PRMPROFILE p, user v
where p.id = v.profile and p.language = 0
and v.userid like '%TEST%'
fetch first 1 row only
回答by user1613212
It will display only the top result
它将只显示最上面的结果
select top 1 * from PRMPROFILE p, user v
where
p.id = v.profile
and p.language = 0
and v.userid like '%TEST%';
