我阅读了前面的一个问题“正确的mySql命令来获取元信息”。这正是我想要的,安德烈M的回答是我大部分问题的答案。我遇到的问题是添加WHERE语句,该语句允许我在查询中只列出男性用户或女性用户。如果使用WHERE语句,我将提取两个meta_键值(gender2和play_county)。我想帮助获得两个元密钥值。
wp_用户表
ID|user_login| user_email |user_pass|date_registered
==================================================
1| me |[email protected] |f239j283r| 2011-01-01
2| her |[email protected]|g234j342q| 2011-02-02
wp_usermeta表
umeta_id|user_id|meta_key |meta_value
==========================================
1 | 1 | play_county | Gwinnett
2 | 1 | gender2 | Male
1 | 2 | play_county | Cobb
2 | 2 | gender2 | Female
以下是我最想要的代码:
SELECT u.id, u.user_email, u.display_name,
MIN(CASE m.meta_key WHEN 'play_county' THEN m.meta_value END) AS County,
MIN(CASE m.meta_key WHEN 'gender2' THEN m.meta_value END) AS Gender
FROM wp_users u
LEFT JOIN wp_usermeta m ON u.ID = m.user_id
AND m.meta_key IN ('play_county', 'gender2')
GROUP BY u.ID
输出如下:
ID | user_email | display_name | County | Gender
====================================================
1 | [email protected] | Me Male | Gwinnett | Male
2 | [email protected]| She Female | Cobb | Female
当我添加Where语句时:
WHERE m.meta_key = 'gender2' AND m.meta_value = 'Female'
我得到这个输出:
ID | user_email | display_name | County | Gender
====================================================
2 | [email protected]| She Female | NULL | Female
如何才能使“playúcounty”值成为正确的值?
最佳答案
对每个键类型使用单独的JOIN
:
SELECT u.id, u.user_email, u.display_name,
mc.meta_value AS County,
mg.meta_value AS Gender
FROM wp_users u JOIN
wp_usermeta mc
ON u.ID = mc.user_id AND
mc.meta_key = 'play_county' JOIN
wp_usermeta mg
ON u.ID = mg.user_id AND
mg.meta_key = 'gender2' AND
mg.meta_value = 'Female';
对于少数不同的键,单独的连接是很好的。如果您有很多想要返回的键,那么条件聚合方法就可以工作——但是对于您想要执行的筛选类型来说,它要麻烦一些。