MySQL:以1对1的方式加入 [英] MySQL: JOINs on 1-to-1 basis

问题描述

我认为，这个问题属于更高级的SQL类别(在这种情况下为MySQL):我有两个表(TABLE_FRUIT，TABLE_ORIGIN-仅是示例名称)，它们具有可以连接的列(fruit_name) .

I think, this problem is of more advanced SQL category (MySQL in this case): I have two tables (TABLE_FRUIT, TABLE_ORIGIN - just example names) which have columns that can be joined (fruit_name).

考虑下图:

TABLE_FRUIT
fruit_id|fruit_name  |variety
--------|----------------------
       1|Orange      |sweet
       2|Orange      |large
       3|Lemon       |wild
       4|Apple       |red
       5|Apple       |yellow
       6|Pear        |early
etc...

TABLE_ORIGIN
fuit_id  |fruit_name|Origin
---------|----------|--------
        1|Apple     | Italy
        2|Pear      | Portugal
        3|Grape     | Italy
        4|Orange    | Spain
        5|Orange    | Portugal
        6|Orange    | Italy
etc...      



Desired Result:     
TABLE_FRUIT_ORIGIN
fuit_id  |fruit_name|Origin
---------|----------|--------
        1|Orange    | Spain
        2|Orange    | Portugal
        3|Apple     | Italy
        4|Pear      | Portugal

表在组成联接(fruit_name)的列中具有多个相同的值.尽管如此，我需要以一对一的方式加入值.换句话说，在TABLE_FRUIT中存在2倍的橙色"值，在TABLE_ORIGIN中存在3倍的橙色"值.我正在寻找两场比赛的结果，一场对西班牙，一场对葡萄牙.必须忽略TABLE_ORIGIN中的意大利值，因为TABLE_FRUIT中没有可用的第三个Orange值与TABLE_ORIGIN中的Orange值匹配.

The tables have multiple identical values in columns that compose the joins(fruit_name). Despite that, I need to join the values on 1-to-1 basis. In other words, there is "Orange" value 2 times in TABLE_FRUIT and 3 times in TABLE_ORIGIN. I am looking for a result of two matches, one for Spain, one for Portugal. Italy value from TABLE_ORIGIN must be ignored, because there is no available third Orange value in TABLE_FRUIT to match Orange value in TABLE_ORIGIN.

我尽力了，但是我在Google上找不到任何相关的内容.例如，我尝试再添加一列record_used并尝试UPDATE，但没有成功.

I tried what I could, but I can not find anything relevant on Google. For example, I tried adding one more column record_used and tried UPDATE but without success.

TABLE_ORIGIN
    fuit_id  |fruit_name|origin     |record_used
    ---------|----------|-----------|-----------
            1|Apple     | Italy     |
            2|Pear      | Portugal  |
            3|Grape     | Italy     |
            4|Orange    | Spain     |
            5|Orange    | Portugal  |
            6|Orange    | Italy     |
    etc...      




UPDATE
    TABLE_FRUIT t1
INNER JOIN
    TABLE_ORIGIN t2
ON
    (t1.fruit_name = t2.fruit_name)
AND
    (t2.record_used IS NULL)
SET
    t2.record_used = 1;

摘要:

• 以一对一的方式查找两个表之间的匹配记录(可能是JOIN)
• 对于TABLE_FRUIT中的每条记录，在TABLE_ORIGIN中仅找到一条(倒数第一条)匹配记录.
• 如果TABLE_ORIGIN中的记录已经与TABLE_FRUIT中的记录匹配一次，则可能不会在同一查询运行中再次考虑该记录.

• Find matching records between two tables on 1-to-1 basis (probably JOIN)
• For each record in TABLE_FRUIT find just one (next first) matching record in TABLE_ORIGIN
• If a record in TABLE_ORIGIN was already matched once with a record from TABLE_FRUIT, it may not be considered again in the same query run.

推荐答案

这是我想到的RANK函数.发表评论后，我意识到mysql在GROUP BY函数上没有内置的RANK，因此不得不找到解决方法.

Here is what I had in mind with RANK function. After commenting, I realized mysql doesn't have a built in RANK over GROUP BY function so had to find this work around.

SELECT * 
FROM   (SELECT fruit_name, 
               @f_rank := IF(@f_name = fruit_name, @f_rank + 1, 1) AS rank, 
               @f_name := fruit_name 
        FROM   table_fruit 
        ORDER  BY fruit_name DESC) f 
       INNER JOIN (SELECT fruit_name, 
                          @f_rank := IF(@f_name = fruit_name, @f_rank + 1, 1) AS 
                          rank, 
                          @f_name := fruit_name 
                   FROM   table_origin 
                   ORDER  BY fruit_name DESC) o 
               ON f.fruit_name = o.fruit_name 
                  AND f.rank = o.rank;

说明:将表中每个水果的每个项目排序.因此，第一张表中的Orange的排名为1和2，Apple也是如此.在第二个表中，Orange的排名为1、2和3，而其他人的排名仅为1.然后，基于名称联接表时，也可以基于排名联接，这样，您将获得Orange排名1和2场比赛，但排名3的橙色将不比赛.

Explanation: Rank each item in the table for each fruit. So Orange in the first table would have rank 1 and 2 and so will Apple. In the second table, Orange will have rank 1, 2 and 3 but others will only have rank 1. Then when joining the tables based on names, you can also join based on rank so that way, you'll get Orange rank 1 and 2 match but Orange with rank 3 will not match.

这是基于我对问题的理解.让我知道需求是否与我在此处给出的有所不同.

This is based on my understanding of the problem. Let me know if the requirement is something different than what I have given here.

这篇关于MySQL:以1对1的方式加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！