如何使从第二个表中提取的数据显示为列而不是行?
我有两个表:表1(id,name,uuid(和表2(t_id,t_uuid,dependent_name,dependen_age(。
注意:每个用户最多只能添加10个从属项。
我使用内部联接来联接这两个表,这在某种程度上是有效的。但是,我希望从表2中提取的记录被填充为列。
示例:
表1记录:
1, Jack, 1521
表2记录:
1, 1521, John, 12
2, 1521, Maria, 7
3, 1521, Larry, 3
所需的结果是一行类似:
1, Jack, John, 12, Maria, 7, Larry, 3
这是我写的查询:
SELECT
table1.name,
table2.dependent_name,
table2.dependent_age,
FROM
table1
INNER JOIN table2
ON table1.uuid = table2.t_uuid;
请帮忙,谢谢。
您可以执行以下操作。由于它总是有10个从属项,您可以为名称和年龄编码10个聚合。我在下面展示了4个。
SELECT p.id, p.pid, p.name,
MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1,
MAX(CASE WHEN t.dr = 1 THEN age END) as age1,
MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2,
MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2,
MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3,
MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3,
MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4,
MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4
FROM Table1 p
join (select id , pid, name, age, DENSE_RANK() OVER (partition by pid order by age) as dr
from Table2) t on p.pid= t.pid
group by p.id, p.pid, p.name
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d324e7654218dae3d1168df2bf529099
如果dense_rank((函数不工作。。试试这个。这是dense_Rank的解决方法,因为看起来mysql不支持dense_Rank
SELECT p.id, p.pid, p.name,
MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1,
MAX(CASE WHEN t.dr = 1 THEN age END) as age1,
MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2,
MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2,
MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3,
MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3,
MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4,
MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4
FROM Table1 p
join ( SELECT pid, name, age,
@dense:=IF(@prev_col1=t.pid, IF(@prev_col3=age, @dense, @dense+1), 1) AS dr,
@prev_col1:=t.pid, @prev_col2:=t.name , @prev_col3:=t.age
FROM (select pid, name, age
from Table2 order by pid) t,
(SELECT @row_num:=1, @dense:=1, @rank:=1, @prev_col1:=NULL, @prev_col2:=NULL, @prev_col3:=NULL) var
) t on p.pid= t.pid
group by p.id, p.pid, p.name
http://sqlfiddle.com/#!9/16101fa/18