在MetaKey与MetaValue场景下实现WHERE子句的问题排查

你的SQL存在的问题 #

• 条件写法错误：错误地将meta_key和meta_value合并为单个字符串（如'Department=health'），不符合wp_usermeta的表结构——该表中meta_key是属性键名，meta_value是对应的值，二者需要分开判断。
• 逻辑矛盾：PM.meta_key = 'Department=health' AND PM.meta_key = 'status_av=on'要求同一行的meta_key同时等于两个不同值，单一行数据不可能满足这个条件，导致结果为空。
• LEFT JOIN被破坏：原SQL用LEFT JOIN是为了保留所有用户，但WHERE子句直接过滤PM表字段，会自动排除无对应meta记录的用户，等价于INNER JOIN，违背了原查询的初衷。

正确的实现方式 #

方法一：使用HAVING子句筛选聚合后字段 #

利用行转列生成的新字段，在聚合完成后用HAVING筛选，逻辑直观清晰：

SELECT 
  P.ID, 
  MAX(IF(PM.meta_key = 'first_name', PM.meta_value, NULL)) AS Name, 
  MAX(IF(PM.meta_key = 'last_name', PM.meta_value, NULL)) AS Last, 
  MAX(IF(PM.meta_key = 'Gender', PM.meta_value, NULL)) AS Gender, 
  MAX(IF(PM.meta_key = 'Department', PM.meta_value, NULL)) AS Department, 
  MAX(IF(PM.meta_key = 'position_title', PM.meta_value, NULL)) AS Dpt, 
  MAX(IF(PM.meta_key = 'region', PM.meta_value, NULL)) AS Region, 
  MAX(IF(PM.meta_key = 'district_dar', PM.meta_value, NULL)) AS District, 
  MAX(IF(PM.meta_key = 'status_av', PM.meta_value, NULL)) AS Status 
FROM wp_users AS P 
LEFT JOIN wp_usermeta AS PM on PM.user_id = P.ID
GROUP BY P.ID 
HAVING Department = 'Health' AND Status = 'on'
ORDER BY P.ID DESC LIMIT 30

方法二：先筛选meta表再聚合（性能更优） #

针对大数据量场景，先在wp_usermeta中筛选出需要的记录，再进行聚合，减少参与计算的数据量：

SELECT 
  P.ID, 
  MAX(IF(PM.meta_key = 'first_name', PM.meta_value, NULL)) AS Name, 
  MAX(IF(PM.meta_key = 'last_name', PM.meta_value, NULL)) AS Last, 
  MAX(IF(PM.meta_key = 'Gender', PM.meta_value, NULL)) AS Gender, 
  MAX(IF(PM.meta_key = 'Department', PM.meta_value, NULL)) AS Department, 
  MAX(IF(PM.meta_key = 'position_title', PM.meta_value, NULL)) AS Dpt, 
  MAX(IF(PM.meta_key = 'region', PM.meta_value, NULL)) AS Region, 
  MAX(IF(PM.meta_key = 'district_dar', PM.meta_value, NULL)) AS District, 
  MAX(IF(PM.meta_key = 'status_av', PM.meta_value, NULL)) AS Status 
FROM wp_users AS P 
LEFT JOIN (
  SELECT user_id, meta_key, meta_value 
  FROM wp_usermeta 
  WHERE (meta_key = 'Department' AND meta_value = 'Health') 
     OR (meta_key = 'status_av' AND meta_value = 'on')
     OR meta_key IN ('first_name', 'last_name', 'Gender', 'position_title', 'region', 'district_dar')
) AS PM on PM.user_id = P.ID
GROUP BY P.ID 
HAVING Department = 'Health' AND Status = 'on'
ORDER BY P.ID DESC LIMIT 30

内容的提问来源于stack exchange，提问作者Geofrey Martin