当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Refactoring multiple left joins

问题描述:

A simplified version of my table layout is as follows:

table item

+----+-------+-----+

| ID | sdesc | ... |

+----+-------+-----+

| 1 | item1 | ... |

+----+-------+-----+

table itemaffectTable (call these qProps for properties with quantities)

+--------+---------+----------+

| itemID | affectID| quantity |

+--------+---------+----------+

| 1 | 2 | 10 | // item 1 has affect 2 for a value of 10

| 1 | 3 | 2 | // item 1 has affect 3 for a value of 2

| 2 | 1 | 5 | // item 2 gets aff 1 for 5

| 2 | 1 | 6 | // item 2 gets aff 1 for 6 which means 11 total

| 3 | 5 | 5 |

+--------+---------+----------+

table itemaffectbyTable (call these bProps they're relevant when present)

+--------+---------+

| itemID | affbyID |

+--------+---------+

| 1 | 6 |

| 3 | 2 |

| 3 | 3 |

+--------+---------+

Sample output:

itemID sdesc qpropID value bpropID

1221 a copper lantern 4 2 5

1221 a copper lantern 18 2 5

1221 a copper lantern 17 -5 5

477 a shade 19 3 4

477 a shade 19 3 6

This is incorrect in two ways. For the first item the affectbyID 5 is repeated 3 times ... this is tolerable. In the second case we have the affectID 19 and affect value 3 being repeated twice, this is not permissible.

Ideally I'd like to see

itemID sdesc qpropID value bpropID

1221 a copper lantern 4 2 5

1221 a copper lantern 18 2 NULL

1221 a copper lantern 17 -5 NULL

477 a shade 19 3 4

477 a shade NULL NULL 6

The main problem is the repetition of the qpropIDs and values since they are additive. If the solution repeats bpropIDs it's no big deal.

** UPDATE **

I attempted to use the FULL JOIN idea to get my results but couldn't seem to zero in.

The closest I've come to the results I want came from using sqlFiddle to get

select i.id, i.sdesc, iaft.affectID, iaft.amount, NULL FROM item i

LEFT JOIN itemaffectTable iaft ON i.id=iaft.itemID

UNION

select i.id, i.sdesc, NULL, NULL, iafbt.affectbyID FROM item i

LEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemID

ORDER BY id

So bottom line the idea is I want to retrieve a list of items which meet the criteria of the filters and then match those items up with their associated affectID and affectbyIDs.

The original is below.

SELECT DISTINCT i.id, i.sdesc, iaft.affectID, iaft.amount, iafbt.affectbyID FROM item i

INNER JOIN itemwearTable iwt ON i.id=iwt.itemID

LEFT JOIN itemaffectTable iaft ON i.id=iaft.itemID

LEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemID

LEFT JOIN itemalignTable iat ON i.id = iat.itemID

LEFT JOIN itemgenderTable igt ON i.id = igt.itemID

LEFT JOIN itemgenreTable igrt ON i.id = igrt.itemID

LEFT JOIN itemclassTable ict ON i.id = ict.itemID

LEFT JOIN itemraceTable irt ON i.id = irt.itemID

WHERE (iat.itemID IS NULL OR iat.alignID = 1)

AND (igt.itemID IS NULL OR igt.genderID = 1)

AND (igrt.itemID IS NULL OR igrt.genreID = 1)

AND (ict.itemID IS NULL OR ict.classID = 1)

AND (irt.itemID IS NULL OR irt.raceID = 1)

AND i.minlvl <= 50

AND iwt.wearlocID=1

ORDER BY sdesc

网友答案:

This really is a strange result you're after - it's a sort of pivoted union or something.

Nevertheless, but here it is, with all the bells and whistles you asked for!

select id, sdesc, affectID, amount, affectbyID
from (select 
    id, sdesc, 
    if(sameid and @affectID = affectID and @amount = amount, null, affectID) as affectID,
    if(sameid and @affectID = affectID and @amount = amount, null, amount) as amount,
    @affectID := affectID,
    @amount := amount,
    if(sameid and @affectbyID = affectbyID, null, affectbyID) as affectbyID,
    @affectbyID := affectbyID
from (select
    if(@id is null, false, @id = id) as sameId,
    @id := id as id,
    sdesc, affectID, amount, affectbyID
from (select distinct
  i.id,
  i.sdesc,
  iaft.affectID,
  iaft.amount,
  iafbt.affectbyID
FROM item i
LEFT JOIN itemaffectTable iaft ON i.id=iaft.itemID
LEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemID
ORDER BY 1,3,4,5
) x) y) z

This makes use of User Defined Variables to remember the previous values for columns.

The logic is reset for every new id value, and other columns are made null if the column (or column pair) has the same value as the previous row.

See a live demo on SQLFiddle

网友答案:

Take a look here. I believe this is what you are looking for. In SSMS I would solve this with a Full Join, but apparently you cant do that in mysql so you need a union to combine the right and left outer joins.

http://www.tutorialspoint.com/sql/sql-full-joins.htm

网友答案:

I don't quite get your expected result... it seems quite irrational to me.

Do you want to do something like, for each item, assume you get item-affect and item-affectedBy separately, and you want to combine the result in a row-based manner? i.e. row 1 of item A in first result will be put together with row 1 of item A in second result: e.g.

Item     Affect               Item     AffectedBy
 1         a                    1         r
 1         b                    
 2         c                    2         s
                                2         t
 3         d
                                4         u
 5         e                    5         v
 5         f                    5         w

You want to see something like:

Item    Affect    AffectedBy
1         a          r
1         b          NULL
2         c          s
2         NULL       t
3         d          NULL
4         NULL       u
5         e          v
5         f          w

Right?

If so, the most straight-forward way you can think of is, first make result of item-affect looks like:

Item     Affect   seq
 1         a        1
 1         b        2
 2         c        1 
 3         d        1
 5         e        1
 5         f        2

Seq column is the "rank" within group of item. Do similar thing in item-affectedBy

Then do a full outer join on item and seq column.

I am not sure how to achieve these in MySQL, in Oracle, the way to generate the seq is something like:

select item_id, affect_id, rank() over (partition by item_id order by rownum) rank
from item_affect
order by item_id, rank;

Try to think if you can do similar thing in MySQL.

For full outer join, if it is not supported, you can always perform a union with left and right join.

分享给朋友:
您可能感兴趣的文章:
随机阅读: