使用什么类型的 JOIN
我将使用什么类型的 JOIN 来使 table1
和 table2
只匹配一次.例如,我有 table1
(40 行)和 table2
(10000 行).但是当我在 table1.LocationArea = table2.Location
What type of JOIN would I use to get table1
and table2
to be matched only once. For example, I have table1
(40 rows) and table2
(10000 rows). But I get table1
repeated over and over when I use a join on table1.LocationArea = table2.Location
What I get: What I wish I could get:
t1.LocationArea,t2.Location t1.LocationArea,t2.Location
--------------------------- ---------------------------
az,az az,az
az,az null,az
ca,ca ca,ca
il,il il,il
tx,tx tx,tx
tx,tx null,tx
az,az null,az
null,il
null,ca
我希望在查询中得到 10000 条记录.
I wish to end up with 10000 records in the query.
我已经尝试过inner join
、left
,并且我正在使用不支持外连接的 ZOHO 报告.
I have tried inner join
, left
, and I am using ZOHO reports which does not support outer join's.
SELECT "table1"."LocationArea", "Location"
FROM "table2"
left join "table1" on "Location" = "table1"."LocationArea"
推荐答案
很明显,您的两个连接列都有重复的值.代替 笛卡尔积,[INNER] JOIN
会产生这样,您希望每一行只使用一次.您可以通过为每个重复项添加一个行号 (rn
) 并另外加入 rn
来实现这一点.
Obviously, you have duplicate values for both of the joining columns. Instead of the Cartesian product an [INNER] JOIN
would produce for this, you want each row to be used only once. You can achieve this by adding a row number (rn
) per duplicate and join on rn
additionally.
除非您有额外的限制(如 FK 约束),否则每个表可以有更多或更少的相同值的重复项 - 但您的问题没有任何内容.要保留所有行,可以使用 FULL [OUTER] JOIN
.但是你想在结果中保留10000条记录,这是table2
的基数.所以它必须是 LEFT[OUTER] JOIN
on table1
(有 40 行) - 并从 table1
中排除可能过多的行.
Each table can have more or fewer dupes for the same value than the other unless you have additional restrictions in place (like a FK constraint) - but there is nothing in your question. To keep all rows one would use a FULL [OUTER] JOIN
. But you want to keep 10000 records in the result, which is the cardinality of table2
. So it must be a LEFT [OUTER] JOIN
on table1
(with 40 rows) - and exclude possible excessive rows from table1
.
SELECT t1."LocationArea", t2."Location"
FROM (
SELECT "Location"
, row_number() OVER (PARTITION BY "Location") AS rn
FROM table2
) t2
LEFT JOIN (
SELECT "LocationArea"
, row_number() OVER (PARTITION BY "LocationArea") AS rn
FROM table1
) t1 ON t1."LocationArea" = t2."Location"
AND t1.rn = t2.rn;
适用于 Postgres 或 SQL Server.MySQL 不支持窗口函数,您需要一个替代品:
Works for Postgres or SQL Server. MySQL doesn't support window functions, you would need a substitute:
- SQL SELECT 最后一个条目无限制
需要说明的是:LEFT JOIN
只是LEFT OUTER JOIN
的简写,因此您已经在使用外连接.你的陈述是一个误解:
To be clear: LEFT JOIN
is just shorthand for LEFT OUTER JOIN
, so you are already using an outer join. Your statement is a misunderstanding:
我使用的 ZOHO 报告不支持外连接.
I am using ZOHO reports which does not support outer join's.
相关文章