mysql - 选择带有字符串列的特定输出

Question

我正在做一份关于酒店的报告，我需要总结一些关于他们预订的董事会的信息。

现在我的输出看起来像这样:

SELECT meal.id, meal.title,
    SUM(CASE WHEN meal.id >= 600 THEN '1' ELSE '0' END) as NoofMeals
    FROM meal
    JOIN itinerary i ON i.id = meal.journeyid
    WHERE i.arrival BETWEEN '2015-01-01' AND '2015-01-01'
    GROUP BY meal.title

我的输出看起来像这样:

ID    | title                  | NoofMeals

608   | Dinner                 | 15
621   | Breakfast              | 478
648   | Lunch                  | 74
649   | Breakfast Box          | 5
655   | Lunch-Box              | 7
659   | American breakfast     | 73
671   | Continental breakfast  | 102
674   | All Inclusive          | 312
689   | All Inclusive from 3pm | 7
693   | Picnic                 | 47

我想要的 Ouput 看起来像这样，并将不同的值放在一起，例如所有早餐项目(早餐、早餐盒、美式早餐和欧陆式早餐)或所有全包服务(全包服务和从下午 3 点开始全包服务)。不幸的是，这些 ID 不是连续的。

title                  | NoofMeals

Dinner                 | 15
Breakfast              | 658
Lunch                  | 81
All Inclusive          | 319
Picnic                 | 7

我不再需要 ID，我只需要能够重命名餐点的名称。

我希望你能帮助我，请放轻松，我是 MySQL 的新手。

提前致谢!

Answer 1

“快速而肮脏”的解决方案将使用字符串函数:

SELECT
  case
    when meal.title like '%Breakfast%' then 'Breakfast'
    when meal.title like '%All inclusive%' then 'All Inclusive'
    else meal.title
  end as title,
  ...
...
GROUP BY
  case
    when meal.title like '%Breakfast%' then 'Breakfast'
    when meal.title like '%All inclusive%' then 'All Inclusive'
    else meal.title
  end

虽然正确的解决方案是添加一个包含膳食类别的列:

id | title                  | category
---|------------------------|--------------
1  | Breakfast              | Breakfast
2  | American breakfast     | Breakfast
3  | Continental breakfast  | Breakfast
4  | All Inclusive          | All Inclusive
5  | All Inclusive from 3pm | All Inclusive

并通过 meal.category 而不是 meal.title 加入特定的膳食和组。