mysql - 带有保存状态的 IF 语句中的 SELECT 和 SELECT

Question

我试图找到一些解决方案来避免第三次选择，但我什至不知道，如何谷歌它。

我的尝试（工作，但看起来很丑）：

SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name')
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u

我想要这样的东西：

SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        SOME_STATE0
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u

标签： mysql

Answer 1

加入表而不是使用子查询。

SELECT u.*, IF(meta_value = '' OR meta_value IS NULL, 'John', meta_value) AS first_name
FROM wp_users AS u
LEFT JOIN usermeta AS m ON m.user_id = u.id AND m.meta_key = 'first_name'