如何使用case语句将行转换为列

Question

我正在访问一个WordPress数据库，我想在进行选择时将行转换为列。到目前为止，这是有效的，但我不知道如何指定条件。

表wp_usermeta

| umeta_id | user_id | meta_key   | meta_value |
| -        | -       | -          | -          |
| 1        | 1       | first_name | james      |
| 2        | 1       | last_name  | wilson     |
| 3        | 1       | zip        | 83902      |
| 4        | 2       | first_name | mason      |
| 5        | 2       | last_name  | burton     |
| 6        | 2       | zip        | 29483      |
| 7        | 3       | first_name | jackson    |
| 8        | 3       | last_name  | owen       |
| 9        | 3       | zip        |            |

表wp_users

| ID | user_email               |
| -  | -                        |
| 1  | [e-mail] |
| 2  | [e-mail] |
| 3  | [e-mail] |

select
user_id,
max(case when meta_key = 'first_name' then meta_value end) first_name,
max(case when meta_key = 'last_name' then meta_value end) last_name,
max(case when meta_key = 'zip' then meta_value end) postal_code,
user_email as email
from `wp_usermeta`
left join `wp_users` on `wp_usermeta`.`user_id` = `wp_users`.`ID`
group by `user_id`;

结果

| user_id | first_name | last_name | postal_code| email                    |
| -       | -          | -         | -          | -                        |
| 1       | james      | wilson    | 83902      | [e-mail] |
| 2       | mason      | burton    | 29483      | [e-mail] |
| 3       | jackson    | owen      | null       | [e-mail] |

我需要这样的条件：

(case when meta_key = 'first_name' and meta_value = 'james' then 'good' else 'bad') test

错误代码：1055。SELECT列表的表达式不在GROUP BY子句中，并且包含非聚合列"wp_usermeta.meta_value"，该列在功能上不依赖于GROUP BY子句的列；这与sql_mode=only_full_group_by 不兼容

max(case when meta_key = 'first_name' and meta_value = 'james' then 'good' else 'bad' end) test

始终返回"好"；(最大值(。

Answer 1

meat_value必须在最大中

但老实说，我不知道你为什么想要帽子，这个查询

CREATE TABLE wp_usermeta
(`umeta_id` varchar(1), `user_id` varchar(1), `meta_key` varchar(10), `meta_value` varchar(7))
;

INSERT INTO wp_usermeta
(`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES
('1', '1', 'first_name', 'james'),
('2', '1', 'last_name', 'wilson'),
('3', '1', 'zip', '83902'),
('4', '2', 'first_name', 'mason'),
('5', '2', 'last_name', 'burton'),
('6', '2', 'zip', '29483'),
('7', '3', 'first_name', 'jackson'),
('8', '3', 'last_name', 'owen'),
('9', '3', 'zip', NULL)
;

CREATE TABLE wp_users
(`ID` varchar(1), `user_email` varchar(24))
;

INSERT INTO wp_users
(`ID`, `user_email`)
VALUES
('1', '[e-mail]'),
('2', '[e-mail]'),
('3', '[e-mail]')
;

select
user_id,
max(case when meta_key = 'first_name' and meta_value = 'james' then meta_value end) first_name,
max(case when meta_key = 'last_name' and meta_value = 'james' then meta_value end) last_name,
max(case when meta_key = 'zip' and meta_value = 'james' then meta_value end) postal_code,
user_email as email
from `wp_usermeta`
left join `wp_users` on `wp_usermeta`.`user_id` = `wp_users`.`ID`
group by `user_id`,user_email;

user_id|first_name|last_name|postal_code|email：-----|：--------|：---------|：---------------|：-----------------------1|james|null|null|[e-mail]2|null|null|null|[e-mail]3 |null|null|null|[e-mail]

db<gt；小提琴这里