mysql 数据透视表什么的

Question

好吧，我还是个 sql 初学者，还无法弄清楚这个问题。我有四个表:公司、人员、详细信息、人员详细信息。

companies: 
id, compname
(1, ACME), 
(2, ACME Group), ...

persons: 
id, name, lastname, company id
(1, donald, duck, 1), 
(2, lucky, luke, 1), 
(3, mickey, mouse, 2)

details: 
id, description
(1, 'weight'), 
(2, 'height'), 
(3, 'haircolor'), ...

person_details: 
id, persons id, details id, value
(1, 1, 1, 70), 
(2, 1, 3, 'red'), 
(3, 2, 1, 90), 
(4, 3, 2, 180)

如您所见，并非所有人都拥有所有详细信息，并且可用详细信息的列表是可变的。

现在，对于给定的人员 ID 和详细信息 ID 数组，我希望获取包含以下内容的行:公司名称和 ID、人员姓名和姓氏、详细名称以及所提供数组中每个详细信息的值。假设 people(1,2)、details(1,3) 的结果应该是:

companies.id, companies.name, name, lastname, details.description, person_details.value,...    
    1, ACME, donald, duck, 'weight', 70, 'haircolor', 'red'
    2, ACEM, lucky, luke, 'weight', 90, 'haircolor', null

请帮忙...

Answer 1

根据您的描述，您似乎想要pivot数据，但不幸的是MySQL没有pivot函数，因此您需要使用聚合函数来复制它带有 CASE 语句。

如果您提前知道描述值，则可以将查询硬编码为以下内容:

select c.id,
  c.compname,
  p.name,
  p.lastname,
  max(case when d.description = 'weight' then pd.value end) weight,
  max(case when d.description = 'haircolor' then pd.value end) haircolor,
  max(case when d.description = 'height' then pd.value end) height
from companies c
left join persons p
  on c.id = p.`company id`
left join person_details pd
  on p.id = pd.`persons id`
left join details d
  on pd.`details id` = d.id
-- where p.id in (1, 2)
group by c.id, c.compname, p.name, p.lastname

参见SQL Fiddle with Demo

如果您有未知数量的值，则可以使用 prepared statement to generate this dynamically与此类似:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN d.description = ''',
      description,
      ''' then pd.value end) AS ',
      description
    )
  ) INTO @sql
FROM details;

SET @sql = CONCAT('SELECT c.id,
                      c.compname,
                      p.name,
                      p.lastname, ', @sql, ' 
                  from companies c
                  left join persons p
                    on c.id = p.`company id`
                  left join person_details pd
                    on p.id = pd.`persons id`
                  left join details d
                    on pd.`details id` = d.id
                  -- where p.id in (1, 2)
                  group by c.id, c.compname, p.name, p.lastname');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见SQL Fiddle with Demo

两个版本都会生成结果:

| ID |   COMPNAME |   NAME | LASTNAME | WEIGHT | HEIGHT | HAIRCOLOR |
---------------------------------------------------------------------
|  1 |       ACME | donald |     duck |     70 | (null) |       red |
|  1 |       ACME |  lucky |     luke |     90 | (null) |    (null) |
|  2 | ACME Group | mickey |    mouse | (null) |    180 |    (null) |