mysql - 使用 SQL 从行中的值创建列

Question

谁能帮我用sql连接两个表？加入后，行应显示为一列，如下所示。

我有两个表 T1 和 T2

T1
------------
Id.  Name  date
--------------
1    AAA    2019-05-06
2    BBB    2019-05-06



T2
---------------
Id.  attr_name. attr_value. date
--------------------
1 .   PP0         8       2019-05-06    
1 .   PD0        125.00     2019-05-06
1     PP1         2         2019-05-06
1     PD1        150.00      2019-05-06
3     PP0         5         2019-05-06
3     PD1        50.00      2019-05-06

有PP0就会有对应的PD0

My output should be
---------------------
Id    Name       attr_pp_name     attr_pp_value.  attr_pd_name     attr_pd_value
1     AAA          PP0                  8               PD0          125.00
1.    AAA          PP1                  2               PD1          150.00

我尝试了如下查询

select t.Id, t.name ea.attr_name ,ea.attr_value, ea1.attr_name, ea1.attr_value from T1 t
INNER JOIN T2 ea ON t.id = ea.id AND ea.attr_name IN ('PP0', 'PP1')
INNER JOIN T2 ea1 ON t.id = ea.id AND ea.attr_name IN ('PD0', 'PD1');

但是上面的查询给出了重复的结果

Id    Name       attr_pp_name     attr_pp_value.  attr_pd_name     attr_pd_value
1     AAA          PP0                  8               PD0          125.00
1.    AAA          PP1                  2               PD1          150.00
1     AAA          PP0                  2               PD1          150.00
1.    AAA          PP1                  8               PD0          125.00

Answer 1

在MySQL 8+中，可以使用窗口函数来枚举属性值，然后聚合:

select t.*,
       max(case when ea.attr_name like 'PP%' then ea.attr_name end),
       max(case when ea.attr_name like 'PP%' then ea.attr_value end),
       max(case when ea.attr_name like 'PD%' then ea.attr_name end),
       max(case when ea.attr_name like 'PD%' then ea.attr_value end)
from T1 t join
     (select ea.*,
             row_number() over (partition by ea.id, left(ea.attr_name, 2) order by ea.id) as seqnum
      from t2 ea
     ) ea
     on ea.id = t.id
group by t.id, ea.seqnum;

编辑:

在早期版本中解决这个问题的一种方法是使用变量:

select t.*,
       max(case when ea.attr_name like 'PP%' then ea.attr_name end),
       max(case when ea.attr_name like 'PP%' then ea.attr_value end),
       max(case when ea.attr_name like 'PD%' then ea.attr_name end),
       max(case when ea.attr_name like 'PD%' then ea.attr_value end)
from T1 t join
     (select ea.*,
             (@rn := if(@ida = concat_ws(':', ea.id, left(ea.attr_name, 2)), @rn + 1,
                        if(@ida := concat_ws(':', ea.id, left(ea.attr_name, 2)), 1, 1)
                       )
             ) as seqnum
      from (select ea.*
            from t2 ea
            order by ea.id, left(ea.attr_name, 2)
           ) ea cross join
           (select @ida := '', @rn := 0) params
     ) ea
     on ea.id = t.id
group by t.id, ea.seqnum;