mysql - 查询仅获取每个唯一列 ID 的 3 条记录

Question

我有一个列出食物类型的表，我希望运行一个查询以返回最多 12 种食物，但每个类别不超过 3 种。我可以在一个查询中完成此操作吗？

category    food    tastyFactor id
seafood     fish    100         1
seafood     prawns  150         2
seafood     crab    50          3
seafood     oysters 300         4
meat        chicken 20          5
meat        pork    100         6
meat        lamb    40          7
meat        beef    50          8
vegetables  carrot  10          9
vegetables  cabbage 300         10
vegetables  potato  75          11
vegetables  parsnip 500         12

食物应按美味因素排序(最低的数字应出现在结果集中较早的位置)。

在我的示例中，结果应该是:

carrot
chicken
lamb
beef
crab
potato
fish
prawns
cabbage

Answer 1

您可以使用UNION组合具有相同内容的多个查询:

SELECT food FROM (
  (SELECT category, food, tastyfactor, id FROM table  
    WHERE category = 'seafood' LIMIT 3)
  UNION
  (SELECT category, food, tastyfactor, id FROM table 
    WHERE category = 'meat' LIMIT 3)
  UNION
  (SELECT category, food, tastyfactor, id FROM table 
    WHERE category = 'vegetables' LIMIT 3)
) AS food_from_cateogry ORDER BY tastyfactor