求助：WordPress中MySQL表内连接与数据透视查询实现

原内连接查询语句 #

SELECT DISTINCT
  `wp_4w9s6k45mv_frmt_form_entry_meta`.`meta_id`,
  `wp_4w9s6k45mv_frmt_form_entry`.`entry_id`,
  `wp_4w9s6k45mv_frmt_form_entry`.`form_id`,
  `wp_4w9s6k45mv_frmt_form_entry_meta`.`meta_key`,
  `wp_4w9s6k45mv_frmt_form_entry_meta`.`meta_value`

FROM
  `xpa642996449419`.`wp_4w9s6k45mv_frmt_form_entry` 
  INNER JOIN `xpa642996449419`.`wp_4w9s6k45mv_frmt_form_entry_meta`
    ON (
      `wp_4w9s6k45mv_frmt_form_entry`.`entry_id` = `wp_4w9s6k45mv_frmt_form_entry_meta`.`entry_id`
    ) 
WHERE form_id = 1580;

查询结果 #

现有无内连接透视查询语句 #

SELECT DISTINCT entry_id,
  
  MAX(CASE WHEN meta_key = "hidden-1" THEN meta_value END) AS 'hidden-1',
  MAX(CASE WHEN meta_key = "hidden-2" THEN meta_value END) AS 'hidden-2',
  MAX(CASE WHEN meta_key = "date-1" THEN meta_value END) AS 'date-1',
  MAX(CASE WHEN meta_key = "name-1" THEN meta_value END) AS 'name-1',
  MAX(CASE WHEN meta_key = "number-1" THEN meta_value END) AS 'number-1',
  MAX(CASE WHEN meta_key = "number-2" THEN meta_value END) AS 'number-2',
  MAX(CASE WHEN meta_key = "number-3" THEN meta_value END) AS 'number-3',
  MAX(CASE WHEN meta_key = "textarea-2" THEN meta_value END) AS 'textarea-2',
  MAX(CASE WHEN meta_key = "textarea-1" THEN meta_value END) AS 'textarea-1'

FROM wp_4w9s6k45mv_frmt_form_entry_meta
GROUP BY
  entry_id
ORDER BY
  entry_id ASC;

优化后的透视查询方案 #

现有查询未过滤目标表单条目，且未关联主表确保数据归属，以下是修正优化后的版本：

SELECT 
  fe.entry_id,
  fe.form_id,
  MAX(CASE WHEN fem.meta_key = 'hidden-1' THEN fem.meta_value END) AS `hidden-1`,
  MAX(CASE WHEN fem.meta_key = 'hidden-2' THEN fem.meta_value END) AS `hidden-2`,
  MAX(CASE WHEN fem.meta_key = 'date-1' THEN fem.meta_value END) AS `date-1`,
  MAX(CASE WHEN fem.meta_key = 'name-1' THEN fem.meta_value END) AS `name-1`,
  -- 转换数值字段类型，方便后续计算
  MAX(CASE WHEN fem.meta_key = 'number-1' THEN CAST(fem.meta_value AS DECIMAL(10,2)) END) AS `number-1`,
  MAX(CASE WHEN fem.meta_key = 'number-2' THEN CAST(fem.meta_value AS DECIMAL(10,2)) END) AS `number-2`,
  MAX(CASE WHEN fem.meta_key = 'number-3' THEN CAST(fem.meta_value AS DECIMAL(10,1)) END) AS `number-3`,
  MAX(CASE WHEN fem.meta_key = 'textarea-2' THEN fem.meta_value END) AS `textarea-2`,
  MAX(CASE WHEN fem.meta_key = 'textarea-1' THEN fem.meta_value END) AS `textarea-1`
FROM `xpa642996449419`.`wp_4w9s6k45mv_frmt_form_entry` fe
INNER JOIN `xpa642996449419`.`wp_4w9s6k45mv_frmt_form_entry_meta` fem
  ON fe.entry_id = fem.entry_id
WHERE fe.form_id = 1580
GROUP BY fe.entry_id, fe.form_id
ORDER BY fe.entry_id ASC;

优化说明 #

1. 精准过滤数据：通过关联主表并设置WHERE fe.form_id=1580，只处理目标表单的条目，避免返回无关数据。
2. 保留归属标识：在结果中保留form_id，明确数据所属表单。
3. 优化数值字段：将number-*类字段转换为DECIMAL类型，便于后续进行数值计算或排序操作。
4. 简化冗余语法：分组操作本身已确保每个entry_id仅返回一行，因此可省略DISTINCT。

内容的提问来源于stack exchange，提问作者Kenneth Israel