How do you find all the values that match the keys in the set if the keys can be repeated?



  • There's a sign. Data

    | id | Key | Value  |
    |----|-----|--------|
    | 1  | 0   | f30467 |
    | 2  | 1   | 406b67 |
    | 3  | 2   | c029bd |
    | 4  | 3   | 5c512f |
    | 5  | 4   | 739fd6 |
    | 6  | 5   | 096b71 |
    | 7  | 6   | b1e8b2 |
    | 8  | 7   | e71828 |
    | 9  | 8   | 8d8566 |
    | 10 | 9   | 94dcdc |
    

    Here is a sample of the necessary sample:

    | Key | Value  |
    |-----|--------|
    | 0   | f30467 |
    | 1   | 406b67 |
    | 2   | c029bd |
    | 2   | c029bd |
    | 0   | f30467 |
    

    I've tried to use that request:

    SELECT `Key`, `Value` FROM `Data` WHERE `Key` IN ('0', '1', '2', '2', '0')
    

    It does not give the right result:

    | Key | Value  |
    |-----|--------|
    | 0   | f30467 |
    | 1   | 406b67 |
    | 2   | c029bd |
    

    Reference to example http://sqlfiddle.com/#!9/6bdbd/4 ♪


    The next request gives the required result, but this is not the best solution for a large set of values. Key:

    SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '0'
    UNION ALL
    SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '1'
    UNION ALL
    SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '2'
    UNION ALL
    SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '2'
    UNION ALL
    SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '0'
    

    Reference to example http://sqlfiddle.com/#!9/6bdbd/4 ♪

    How to get the results I need without using the design UNION ALL?



  • mysql does not provide the necessary operators or unnestNo values in the block fromfamiliar postgresql and nothing else in return. Even the 5.7 JSON cannot be unpacked into a set of lines. So the only thing you can try with your request is to rewrite it in. join:

    select `Key`, `Value`
        from `Data`
            join (
                select 0 as `Key`
                union all
                select 1
                union all
                select 2
                union all
                select 2
                union all
                select 0
            ) targetkeys using(`Key`)
    

    But I doubt it'll be better than your request. And it doesn't seem to guarantee the order.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2