mysql - 子查询或嵌套。我真的不知道。也许两者都不是

Question

好吧，我可能在搜索中错过了这个，但我不确定我在找什么。我有几个表格需要从中生成邮件标签。主表有名字和姓氏。然后第二个表有多个数据行，如 phon-123415324，地址 1-1 james st 等......还有一个绑定(bind)数据的表。这是一个示例结果和查询。

SELECT
users.name,
users.surname,
details.`field`,
details.value
FROM
users
Inner Join details ON details.user_id = users.id
Inner Join bookings ON bookings.guest_id = users.id
WHERE
bookings.sub_event_id =  '78'

---

NAME, SURNAME, field,  value
David   Oden    title   Mr
David   Oden    sex Male
David   Oden    mobile  0534600594
David   Oden    company Fterns Group
David   Oden    position    
David   Oden    address_1   Cnr wtrewr Rd & wert St
David   Oden    address_2   
David   Oden    suburb  wertt Mile wertw
David   Oden    state   MAS
David   Oden    postcode    14113
David   Oden    country USA

我需要什么 我需要将 1 行的所有数据导出到 excel 以用于邮寄标签。对不起，如果这真的很愚蠢。我花了大约 4 个小时进行研究，但运气不佳。

我需要类型作为列标题，值作为数据。

应该读

Title | Name | Surname | Mobile | Company | Address etc.....

MR | David | Oden | 0534600594 | Fterns Group | etc....

任何帮助将不胜感激/

Answer 1

这被称为 PIVOT 函数，但不幸的是 MySQL 没有 PIVOT，因此您必须使用聚合函数和 CASE 来复制它 语句。如果您知道需要包含在查询中的值，则可以对它们进行硬编码，类似于以下内容:

select 
  min(case when d.field = 'title' then d.value end) as Title,
  u.name,
  u.surname,
  min(case when d.field = 'sex' then d.value end) as Sex,
  min(case when d.field = 'mobile' then d.value end) as Mobile,
  min(case when d.field = 'company' then d.value end) as Company,
  min(case when d.field = 'position' then d.value end) as Position,
  min(case when d.field = 'address_1' then d.value end) as Address_1,  
  min(case when d.field = 'address_2' then d.value end) as Address_2,  
  min(case when d.field = 'suburb' then d.value end) as suburb,  
  min(case when d.field = 'state' then d.value end) as State,
  min(case when d.field = 'postcode' then d.value end) as Postcode,
  min(case when d.field = 'country' then d.value end) as country
from users u
left join details d
  on u.id = d.id
left join bookings b
  on u.id = b.guest_id
where b.sub_event_id = 78
group by u.name, u.surname

参见 SQL Fiddle with demo

但是如果你有未知数量的列或者如果值会改变，那么你会想要动态地执行这个，那么你应该阅读下面关于准备好的语句的文章:

Dynamic pivot tables (transform rows to columns)

您的代码如下所示:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'min(case when d.field = ''',
      d.field,
      ''' then d.value end) AS ',
      d.field
    )
  ) INTO @sql
from users u
left join details d
  on u.id = d.d_id;

SET @sql = CONCAT('SELECT u.name,
                    u.surname, ', @sql, ' 
                  from users u
                  left join details d
                    on u.id = d.d_id
                  left join bookings b
                    on u.id = b.guest_id
                  where b.sub_event_id = 78
                  group by u.name, u.surname');

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

参见 SQL Fiddle with Demo