更新时间:2023-02-05 09:59:49
这种数据转换类型称为PIVOT. MySQL没有枢轴函数,但是您可以使用带有CASE
表达式的聚合函数来复制它:
This type of data transformation is known as a PIVOT. MySQL doesn't have a pivot function but you can replicate it using an aggregate function with a CASE
expression:
select t1.id,
t1.name,
max(case when t2.`key` = 'address' then t2.value end) address,
max(case when t2.`key` = 'city' then t2.value end) city,
max(case when t2.`key` = 'region' then t2.value end) region,
max(case when t2.`key` = 'country' then t2.value end) country,
max(case when t2.`key` = 'postal_code' then t2.value end) postal_code,
max(case when t2.`key` = 'phone' then t2.value end) phone
from table1 t1
left join table2 t2
on t1.id = t2.id
group by t1.id, t1.name
请参见带演示的SQL提琴.
这也可以在您的table2
上使用多个联接来编写,并且您将在每个key
的联接上包括一个过滤器:
This could also be written using multiple joins on your table2
and you would include a filter on the join for each key
:
select t1.id,
t1.name,
t2a.value address,
t2c.value city,
t2r.value region,
t2y.value country,
t2pc.value postal_code,
t2p.value phone
from table1 t1
left join table2 t2a
on t1.id = t2a.id
and t2a.`key` = 'address'
left join table2 t2c
on t1.id = t2c.id
and t2c.`key` = 'city'
left join table2 t2r
on t1.id = t2r.id
and t2c.`key` = 'region'
left join table2 t2y
on t1.id = t2y.id
and t2c.`key` = 'country'
left join table2 t2pc
on t1.id = t2pc.id
and t2pc.`key` = 'postal_code'
left join table2 t2p
on t1.id = t2p.id
and t2p.`key` = 'phone';
请参见带演示的SQL提琴.
如果您的key
值数量有限,则上述两个版本将非常有用.如果您有未知数量的值,那么您将需要使用准备好的语句来生成动态SQL:
The above two versions will work great if you have a limited number of key
values. If you have an unknown number of values, then you will want to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when t2.`key` = ''',
`key`,
''' then t2.value end) AS `',
`key`, '`'
)
) INTO @sql
from Table2;
SET @sql
= CONCAT('SELECT t1.id, t1.name, ', @sql, '
from table1 t1
left join table2 t2
on t1.id = t2.id
group by t1.id, t1.name;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见带有演示的SQL提琴
所有版本都会给出结果:
All versions will give a result:
| ID | NAME | ADDRESS | CITY | REGION | COUNTRY | POSTAL_CODE | PHONE |
|----|------|----------|--------|--------|---------|-------------|-----------|
| 1 | Jim | X Street | NY | (null) | (null) | (null) | 123456789 |
| 2 | Bob | (null) | (null) | (null) | (null) | (null) | (null) |
| 3 | John | (null) | (null) | (null) | (null) | (null) | (null) |