MySQL 使用表 JOIN 更改数据集..我所问的可能吗？

Question

这个问题比较复杂，请耐心解答。我使用 3 个不同的表来生成 1 个结果集。它们如下:

customer_address_entity
    entity_id | entity_type_id | attribute_set_id | increment_id | parent_id | create_at | update_at | is_active

customer_entity_int
    value_id | entity_type_id | attribute_id | entity_id | value


customer_address_entity_varchar
    value_id | entity_type_id | attribute_id | entity_id | value

好的，现在您已经有了结构，这是我迄今为止构建的 SQL 调用:

SELECT CAE.entity_id, 
       CEI.value AS default_entity_id, 
       CAEV.attribute_id, 
       CAEV.value 
FROM   customer_address_entity AS CAE 
       JOIN customer_entity_int AS CEI 
         ON CEI.entity_id = CAE.parent_id 
            AND CEI.attribute_id = '13' 
       JOIN customer_address_entity_varchar AS CAEV 
         ON CAEV.entity_id = CAE.entity_id 
WHERE  CAE.parent_id = '2328' 
       AND CAE.is_active = 1 

这将输出以下示例数据集:

 ID     default  att   value

'1567', '1567', '19', 'John'
'1567', '1567', '21', 'Doe'
'1567', '1567', '23', 'Johns Company'
'1567', '1567', '25', 'Johns City'
'1567', '1567', '26', 'Johns Country'
'1567', '1567', '27', 'Johns State'
'1567', '1567', '29', 'Johns Zip Code'
'1567', '1567', '30', 'Johns Phone'
'1567', '1567', '31', 'Johns Fax'
'1568', '1567', '19', 'Jane'
'1568', '1567', '21', 'Doe'
'1568', '1567', '23', 'Janes Company'
'1568', '1567', '25', 'Janes City'
'1568', '1567', '26', 'Janes Country'
'1568', '1567', '27', 'Janes State'
'1568', '1567', '29', 'Janes Zip'
'1568', '1567', '30', 'Janes Phone'
'1568', '1567', '31', 'Janes Fax'
'1569', '1567', '19', 'Frank'
'1569', '1567', '21', 'Frunz'
'1569', '1567', '23', 'Franks Company'
'1569', '1567', '25', 'Franks City'
'1569', '1567', '26', 'Franks Country'
'1569', '1567', '27', 'Franks State'
'1569', '1567', '29', 'Franks Zip'
'1569', '1567', '30', 'Franks Phone'
'1569', '1567', '31', 'Franks Fax'

这段代码的最后一部分，我想根据UNIQUE entity_id(返回的第1列)创建X个ROWS(在本例中为3)数据集即1567、1568和1569)。预期的最终结果是:

'1567', '1567', 'John', 'Doe', 'Johns Company', 'Johns City', 'Johns State', 'Johns Zip Code', 'Johns Phone', 'Johns Fax'
'1568', '1567', 'Jane', 'Doe', 'Janes Company', ...  etc
'1569', '1567', 'Frank', 'Franz', 'Franks Comapny', ...   etc        

这可能吗？

编辑 感谢 Gordon Linoff——答案优雅而简单!我自己做了一些编辑，但会接受戈登的回答并投票赞成。这是我所做的编辑，效果非常好!!

select entity_id,
   if(entity_id = default_entity_id, 'true', 'false') as default_entity,
   max(case when attr = '19' then `value` end) as `FirstName`,
   max(case when attr = '21' then `value` end) as `LastName`,
   max(case when attr = '23' then `value` end) as `CompanyName`,
   max(case when attr = '25' then `value` end) as `City`,
   max(case when attr = '27' then `value` end) as `State`,
   max(case when attr = '29' then `value` end) as `ZipCode`,
   max(case when attr = '30' then `value` end) as `PhoneNumber`,
   max(case when attr = '31' then `value` end) as `Fax`

from (SELECT CAE.entity_id, CEI.value AS default_entity_id, CAEV.attribute_id AS attr, CAEV.value 
  FROM   customer_address_entity CAE 
         JOIN customer_entity_int CEI 
           ON CEI.entity_id = CAE.parent_id 
              AND CEI.attribute_id = '13' 
         JOIN customer_address_entity_varchar CAEV 
           ON CAEV.entity_id = CAE.entity_id 
  WHERE  CAE.parent_id = '2328' 
         AND CAE.is_active = 1
 ) as t
group by entity_id

Answer 1

您可以使用分组依据来做到这一点:

select entity_id,
       MAX(default) as default,
       max(case when att = '19' then value end) as FirstName,
       max(case when att = '21' then value end) as LastName,
       max(case when att = '23' then value end) as CompanyName,
       max(case when att = '25' then value end) as City,
       max(case when att = '27' then value end) as State,
       max(case when att = '29' then value end) as ZipCode,
       max(case when att = '30' then value end) as PhoneNumber,
       max(case when att = '31' then value end) as Fax
from (SELECT CAE.entity_id, CEI.value AS default_entity_id, CAEV.attribute_id, CAEV.value 
      FROM   customer_address_entity CAE 
             JOIN customer_entity_int CEI 
               ON CEI.entity_id = CAE.parent_id 
                  AND CEI.attribute_id = '13' 
             JOIN customer_address_entity_varchar CAEV 
               ON CAEV.entity_id = CAE.entity_id 
      WHERE  CAE.parent_id = '2328' 
             AND CAE.is_active = 1
     ) t
group by entity_id

这个过程称为pivoting，而聚合是一种解决方案(某些数据库对此有一个pivot关键字)。这假设每个值每个实体仅出现一次。另外，如果某个值不存在，它将得到 NULL 值。