如何将此LEFT JOIN返回的行数限制为一? [英] How do I limit the number of rows returned by this LEFT JOIN to one?

问题描述

所以我认为我已经看到了解决方案，但是它们都是非常复杂的查询.我在oracle 11g中供参考.

So I think I've seen a solution to this however they are all very complicated queries. I'm in oracle 11g for reference.

我拥有的是简单的一对多连接，效果很好，但是我不需要那么多连接.我只希望左边的表(一个)只连接满足连接条件的任何1行...不多的行.

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.

我需要这样做，因为查询是在COUNTS的汇总中进行的，因此，如果我进行常规的左联接，我会得到5行，而我只应该得到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.

因此示例数据如下:

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

我需要做的是在ASSIGNMENT = ASSIGNMENT_GROUP上联接这两个表，但是只返回1行.

what I need to do is join these two tables on ASSIGNMENT=ASSIGNMENT_GROUP but only have 1 row returned.

当我进行左联接时，我得到三行返回的beaucse，这是左联接的本质

when I do a left join I get three rows returned beaucse that is the nature of hte left join

推荐答案

如果oracle支持行号(分区依据)，则可以创建一个子查询来选择行等于1的地方.

If oracle supports row number (partition by) you can create a sub query selecting where row equals 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

这篇关于如何将此LEFT JOIN返回的行数限制为一?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！