嵌套案例语句逻辑

Question

我正在处理一个查询，以扁平化WordPress数据库中的一些wp_postsmeta数据，并且需要在各种meta_values上设置特定值。 我有一个类别键和各种类别值，对于每个值，我想为我们的品牌调色板设置一个名为 color 的列，并带有命名颜色

select post_title as title, 
MAX(CASE WHEN meta_key='corporate_calendar_category' THEN meta_value END) as 'category',
MAX(CASE WHEN meta_key = 'corporate_calendar_subcategory' THEN meta_value END) as 'subcategory',
// Do I need to include a nested CASE WHEN?
MAX(CASE WHEN meta_key = 'corporate_calendar_subcategory' and meta_value = 'Marketing' THEN 'blueLagoon' END) as 'color',
MAX(CASE WHEN meta_key = 'corporate_calendar_presenter' THEN meta_value END) as 'presenter',
MAX(CASE WHEN meta_key = 'corporate_calendar_date' THEN meta_value END) as 'start_date',
MAX(CASE WHEN meta_key = 'corporate_calendar_time' THEN meta_value END) as 'start_time',
MAX(CASE WHEN meta_key = 'corporate_calendar_duration' THEN meta_value END) as 'duration',
MAX(CASE WHEN meta_key = 'corporate_calendar_registration_link' THEN meta_value END) as 'registration_link',
MAX(CASE WHEN meta_key = 'corporate_calendar_description' THEN meta_value END) as 'description',
MAX(CASE WHEN meta_key = 'corporate_calendar_image_path' THEN meta_value END) as 'image_path'
FROM   wp_posts p 
JOIN wp_postmeta m ON p.id = m.post_id
where p.post_type = 'calendar-event'
and p.post_status = 'publish'
GROUP BY p.id

corporate_calendar_subcategory有很多值，营销，人力资源，公司假期等，对于每个子类别，我希望每一行都有特定的颜色。

title     category   subcategory       color        presenter   start_date etc.
Example   Training   Marketing         blueLagoon   someone     08/29/2018
Labor Day Reminder   Company Holiday   camelot                  09/03/2018
etc
etc

实现此目的的最佳方法是使用嵌套的 CASE WHEN 来对抗meta_value？还是有更好的方法？

我还尝试包含一个 if 语句(如下(，但这重复了每一行。

if(meta_key = 'corporate_calendar_subcategory', 
CASE
WHEN meta_value = 'Marketing' THEn 'blueLagoon'
WHEN meta_value = 'Company Holiday' THEN 'camelot'
END,
'') as color,

Answer 1

是的，如果您事先知道所有类别和颜色，嵌套的CASE语句将是正确的方法。如果类别颜色位于另一个表中，则可以改为执行子选择或JOIN。

下面是一个嵌套大小写的示例：

max(case when meta_key = 'corporate_calendar_subcategory' then
case meta_value
when 'Marketing' then 'blue'
when 'Sales' then 'yellow
when 'Development' then 'red'
end
end)