SQL 如何将此 LEFT JOIN 返回的行数限制为一？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10236229/
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 do I limit the number of rows returned by this LEFT JOIN to one?
提问by BostonMacOSX
So I think I've seen a solution to this however they are all very complicated queries. I'm in oracle 11g for reference.
所以我想我已经看到了一个解决方案，但是它们都是非常复杂的查询。我在 oracle 11g 中以供参考。
What I have is a simple one to many join which works great however I don't need the many. I just want the left table (the one) to just join any 1 row which meets the join criteria...not many rows.
我拥有的是一个简单的一对多连接，效果很好，但我不需要很多。我只希望左表（那个）只加入符合加入条件的任何 1 行……行数不多。
I need to do this because the query is in a rollup which COUNTS so if I do the normal left join I get 5 rows where I only should be getting 1.
我需要这样做，因为查询在一个 COUNTS 的汇总中，所以如果我进行正常的左连接，我会得到 5 行，而我应该只得到 1。
So example data is as follows:
所以示例数据如下：
TABLE 1:
-------------
TICKET_ID ASSIGNMENT
5 team1
6 team2
TABLE 2:
-------------
MANAGER_NAME ASSIGNMENT_GROUP USER
joe team1 sally
joe team1 stephen
joe team1 louis
harry team2 ted
harry team2 thelma
what I need to do is join these two tables on ASSIGNMENT=ASSIGNMENT_GROUP but only have 1 row returned.
我需要做的是在 ASSIGNMENT=ASSIGNMENT_GROUP 上加入这两个表，但只返回 1 行。
when I do a left join I get three rows returned beaucse that is the nature of hte left join
当我进行左连接时，我得到三行返回的beaucse，这是左连接的性质
采纳答案by Chris Moutray
If oracle supports row number (partition by) you can create a sub query selecting where row equals 1.
如果 oracle 支持行号（分区依据），您可以创建一个子查询，选择行等于 1 的位置。
SELECT * FROM table1
LEFT JOIN
(SELECT *
FROM (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY assignmentgroup ORDER BY assignmentgroup) AS Seq
FROM table2) a
WHERE Seq = 1) v
ON assignmet = v.assignmentgroup
回答by Justin Cave
You could do something like this.
你可以做这样的事情。
SELECT t1.ticket_id,
t1.assignment,
t2.manager_name,
t2.user
FROM table1 t1
LEFT OUTER JOIN (SELECT manager_name,
assignment_group,
user,
row_number() over (partition by assignment_group
--order by <<something>>
) rnk
FROM table2) t2
ON ( t1.assignment = t2.assignment_group
AND t2.rnk = 1 )
This partitions the data in table2by assignment_groupand then arbitrarily ranks them to pull one arbitrary row per assignment_group. If you care which row is returned (or if you want to make the row returned deterministic) you could add an ORDER BYclause to the analytic function.
这将数据分区为table2by assignment_group，然后任意排列它们以每个 提取任意行assignment_group。如果您关心返回哪一行（或者如果您想让该行返回确定性），您可以ORDER BY向分析函数添加一个子句。
回答by Luiggi Mendoza
In Oracle, if you want 1 result, you can use the ROWNUMstatement to get the first N values of a query e.g.:
在 Oracle 中，如果您想要 1 个结果，您可以使用该ROWNUM语句来获取查询的前 N 个值，例如：
SELECT *
FROM TABLEX
WHERE
ROWNUM = 1 --gets the first value of the result
The problem with this single query is that Oracle never returns the data in the same order. So, you must oder your data before use rownum:
这个单一查询的问题在于 Oracle 从不以相同的顺序返回数据。因此，您必须在使用 rownum 之前对数据进行排序：
SELECT *
FROM
(SELECT * FROM TABLEX ORDER BY COL1)
WHERE
ROWNUM = 1
For your case, looks like you only need 1 result, so your query should look like:
对于您的情况，看起来您只需要 1 个结果，因此您的查询应如下所示：
SELECT *
FROM
TABLE1 T1
LEFT JOIN
(SELECT *
FROM TABLE2 T2 WHERE T1.ASSIGNMENT = T2.ASSIGNMENT_GROUP
AND
ROWNUM = 1) T3 ON T1.ASSIGNMENT = T3.ASSIGNMENT_GROUP
回答by Paul
In MySQL you could just GROUP BY ASSIGNMENT and be done. Oracle is more strict and refuses to just choose (in an undefined way) which values of the three rows to choose. That means all returned columns need to be part of GROUP BY or be subject to an aggregat function (COUNT, MIN, MAX...)
在 MySQL 中，您可以通过 GROUP BY ASSIGNMENT 完成。Oracle 更严格，拒绝只是选择（以未定义的方式）要选择三行的哪些值。这意味着所有返回的列都需要是 GROUP BY 的一部分或受聚合函数的约束（COUNT、MIN、MAX...）
You can of course choose to just don't care and use some aggregat function on the returned columns.
您当然可以选择不关心并在返回的列上使用一些聚合函数。
select TICKET_ID, ASSIGNMENT, MAX(MANAGER_NAME), MAX(USER)
from T1
left join T2 on T1.ASSIGNMENT=T2.ASSIGNMENT_GROUP
group by TICKET_ID, ASSIGNMENT
If you do that I would seriously doubt that you need the JOIN in the first place.
如果你这样做，我会严重怀疑你首先需要 JOIN。
MySQL could also help with GROUP_CONCAT in the case that you want a string concatenation of group values for a column (humans often like that), but with Oracle that is staggeringly complex.
MySQL 也可以帮助使用 GROUP_CONCAT，如果您想要一个列的组值的字符串连接（人们通常喜欢这样），但 Oracle 的复杂程度惊人。
Using a subquery as already suggested is an option, look herefor an example. It also allows you to sort the subquery before selecting the top row.
使用已经建议的子查询是一种选择，请在此处查看示例。它还允许您在选择顶行之前对子查询进行排序。
回答by aldo
I think what you need is to use GROUP BYon the ASSIGNMENT_GROUPfield.
我认为你需要的是GROUP BY在ASSIGNMENT_GROUP场上使用。
回答by ZERO
you can use subquery - select top 1
您可以使用子查询 - 选择前 1
