MySQL: JOINs on 1-to-1 basis

问题:

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).

Consider the following diagram:

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

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.

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;

Summary:

• 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.

回答1:

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;

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.

回答2:

There is an arbitrary relationship between the number of entries and the order of those entries, so use techniques to match the number of items and order of those items. In MariaDB v10 which supports "window functions" dense_rank() and row_number() this is relatively easy:

select 
       row_number() over(order by fn.fruit_id) as fruit_id
      , fn.fruit_name, o.Origin, fn.variety
from (
     select fruit_name, variety, fruit_id
          , dense_rank() over(partition by fruit_name order by fruit_id) rnk
     from table_fruit
    ) fn
inner join (
     select fruit_name, Origin
          , dense_rank() over(partition by fruit_name order by fruit_id) rnk
     from table_origin
    ) o on fn.fruit_name = o.fruit_name and fn.rnk = o.rnk

fruit_id | fruit_name | Origin   | variety
-------: | :--------- | :------- | :------
       1 | Orange     | Spain    | sweet  
       2 | Orange     | Portugal | large  
       3 | Apple      | Italy    | red    
       4 | Pear       | Portugal | early  

dbfiddle here

A pure MySQL solution is a bit more complex because it requires use of @variables that will substitute for those window functions.