MySQL-在列中进行一条记录 [英] MySQL - Make one record out of a column
问题描述
我无法弄清楚这一点.
我有这张桌子:
id component_id data
1 1 tests@dsaasd.com
1 2 firstname1
1 3 lastname1
1 4 phone1
2 1 email2
2 2 firstname2
2 3 lastname2
2 4 phone2
现在,我想查询一个将根据ID提取所有数据的查询.我想要这些记录:
Now I want to have a query that will extract all the data according to the id. I want to have these records:
1 tests@dsaasd.com firstname1 lastname1 phone1
2 email2 firstname2 lastname2 phone2
我希望我能理解....
I hope I'm understood....
推荐答案
您要的基本上是PIVOT
,但是MySQL没有数据透视功能,因此您可以使用CASE
语句将其复制功能.
What you are asking for is basically a PIVOT
but MySQL does not have a pivot function so you can replicate this using a CASE
statement with an aggregate function.
如果您知道这些值,则可以对解决方案进行硬编码,类似于以下内容:
If you know the values, then you can hard-code the solution similar to this:
select id,
max(case when component_id = 1 then data end) Email,
max(case when component_id = 2 then data end) Firstname,
max(case when component_id = 3 then data end) Lastname,
max(case when component_id = 4 then data end) Phone
from yourtable
group by id;
请参见带有演示的SQL提琴
结果是:
| ID | EMAIL | FIRSTNAME | LASTNAME | PHONE |
-----------------------------------------------------------
| 1 | tests@dsaasd.com | firstname1 | lastname1 | phone1 |
| 2 | email2 | firstname2 | lastname2 | phone2 |
我猜您有一个表将component_id与名称相关联,因此您的查询也可能是:
I am guessing that you have a table to associate the component_id with a name so your query could also be:
select t1.id,
max(case when t2.name = 'email' then data end) Email,
max(case when t2.name= 'FirstName' then data end) Firstname,
max(case when t2.name= 'LastName' then data end) Lastname,
max(case when t2.name= 'phone' then data end) Phone
from yourtable t1
inner join component t2
on t1.component_id = t2.id
group by t1.id;
请参见带有演示的SQL提琴
如果值的数量未知,则可以使用准备好的语句动态生成此查询:
If you have an unknown number of values, then you can use a prepared statement to generate this query dynamically:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when name = ''',
name,
''' then data end) AS ',
name
)
) INTO @sql
FROM component;
SET @sql = CONCAT('SELECT t1.id, ', @sql, '
from yourtable t1
inner join component t2
on t1.component_id = t2.id
group by t1.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见带有演示的SQL提琴
所有版本都会为您带来相同的结果.
All versions will give you the same result.
这篇关于MySQL-在列中进行一条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!