There is such a label 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 required sample:

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

I tried to use this query:

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

It does not give the desired result:

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

Reference to an example in SQL Fiddle .

The following query gives the desired result, but this is not the most optimal solution for a large set of Key values:

 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 an example in SQL Fiddle .

How to get the result I need without using the UNION ALL construction?

  • and what's the point of getting several times the same record? get unique ones and arrange them in whatever way you like - splash58
  • @ splash58, this is now done in PL, however, I would like to shift this task onto the shoulders of the database, if it is possible to do without a few queries. - VenZell

1 answer 1

mysql does not provide the necessary operators, neither unnest nor values in the from block, familiar from postgresql and nothing else in return. Even JSON that appeared in 5.7 cannot be unpacked into a rowset. Therefore, the only thing you can try to do with your query variant 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 that it will be more profitable than your variant of the request. And, like, does not guarantee the order.

  • It's sad of course. We'll have to leave data processing as it is (in PL). - VenZell