mysql - mySQL 中的“紧凑”SELECT 结果

Question

是否有办法“压缩”SQL 查询的结果？

结果如下:

+----+-----------+----------+-----------+-------------+--------+
| ID | Firstname | Lastname |   Hobby   |     Job     |  Age   |
+----+-----------+----------+-----------+-------------+--------+
| 1  | John      | Doe      | (null)    | (null)      | 30     |
| 1  | John      | Doe      | Chess     | (null)      | (null) |
| 2  | Adam      | Jackson  | (null)    | Accountant  | (null) |
| 2  | Adam      | Jackson  | (null)    | (null)      | 55     |
| 3  | Michael   | Smith    | Knitting  | (null)      | (null) |
| 3  | Michael   | Smith    | (null)    | Banker      | (null) |
+----+-----------+----------+-----------+-------------+--------+

但我希望它看起来像这样:

+----+-----------+----------+-----------+-------------+--------+
| ID | Firstname | Lastname |   Hobby   |     Job     |  Age   |
+----+-----------+----------+-----------+-------------+--------+
| 1  | John      | Doe      | Chess     | (null)      | 30     |
| 2  | Adam      | Jackson  | (null)    | Accountant  | 55     |
| 3  | Michael   | Smith    | Knitting  | Banker      | (null) |
+----+-----------+----------+-----------+-------------+--------+

我尝试使用GROUP BY，但它只接受数据库中每个人的第一个自定义字段。

我在这里设置了一个 SQL Fiddle:http://sqlfiddle.com/#!9/39563/2

设置有点奇怪，但由于我正在处理的数据库，我需要保留该结构。

Answer 1

您可以使用 max 聚合函数和 group by 来展平结果，如下所示:

select 
  p.id as "User ID", 
  p.firstname Firstname, 
  p.lastname as Lastname,
  max(case when cf.fieldname = 'Hobby' then cfv.value end) as "Hobby",
  max(case when cf.fieldname = 'Job'   then cfv.value end) as "Job",
  max(case when cf.fieldname = 'Age'   then cfv.value end) as "Age"
from CustomFields cf 
join CustomFieldValues cfv on cfv.fieldid = cf.id
join People p on cfv.relid = p.id
where cf.fieldname in ('Hobby', 'Job', 'Age')
group by p.id, p.firstname, p.lastname 
order by p.id;

此外，字符串文字应该用单引号引起来，并且您可以使用别名来减少查询文本并使其更具可读性。

Your Fiddle, updated