mysql - 查询问题，插入和匹配

Question

ID      food        MONTH   Cost
523     VEG         Jan     50.00 
523     HOT DOG     Feb     40.00 
523     VEG         Feb     55.00 
523     COOKIE      Feb     10.00 
984     VEG         Mar     60.00 
427                 Jan     15.00 
644     HOT DOG     Feb     35.00 
644     FRIES       Feb      2.00 
644     HOT DOG     Mar     40.00 

我需要在显示蔬菜的食物 (food2) 之后插入一列

where veg=Vegatable, HOT DOG=Hot Dog, COOKIE=Cookie, 

格式正确（大写第一个字母），如果不是这三个中的一个，hot dog，cookie 和 vegatbale，它等于 other

ID  Food    food2           MONTH   REVENUE
523 VEG     Vegetable       Jan           50.00 
523 HOT DOG Hot Dog         Feb           40.00 
523 VEG     Vegetable       Feb           55.00 
523 COOKIE  Cookie          Feb           10.00 
984 VEG     Vegetable       Mar           60.00 
427         Other           Jan           15.00 
644 HOT DOG Hot Dog         Feb           35.00 
644 FRIES   Other           Feb            2.00 
644 HOT DOG Hot Dog         Mar           40.00

标签： mysqlsql

Answer 1

Select ID, 
       Food, 
       CASE WHEN Food = ‘VEG’ THEN ‘Vegetable’ 
            WHEN Food = ‘HOT DOG’ THEN ‘Hot Dog’ 
            WHEN Food = ‘COOKIE’ THEN ‘Cookie’ 
            ELSE ‘Other’ END AS Food2, 
       MONTH, 
       REVENUE
FROM MyTable