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

[gionna]

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?

[Trenton]

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.