mysql 问题求助

[sd116460]

一张表是 存放各个平台对应的平台名和平台名对应的id

drop table if exists center_data_report.all_platform_info;
create table center_data_report.all_platform_info(
id int auto_increment primary key not null ,
pf_id int,
pf_name varchar(20)
);

insert into center_data_report.all_platform_info (pf_id, pf_name)
values (1, 'QQ音乐')
, (3, '网易云音乐')
, (6, '微博')
, (7, '抖音')
, (8, '酷我音乐')
, (9, '酷狗')
, (13, '虾米音乐')
, (14, '快手')
, (18, 'B站')
, (19, '小红书');

然后另外一张表是一个人在平台上的数据情况

drop table if exists center_data_report.artist_platform_info;
create table center_data_report.artist_platform_info(
id int auto_increment primary key not null ,
pf_id int,
pf_name varchar(20),
person varchar(100),
hot_heat int
);

insert into center_data_report.artist_platform_info (pf_id,pf_name,person,hot_heat),values (1,'QQ音乐','张三','100')

要怎么做能显示出的结果是 张三这个人在所有平台的hot_heat,
最后的结果是这样的：

pf_id pf_name person hot_heat
1 QQ音乐 张三 100
3 网易 张三 null
6 微博 张三 null
7 抖音 张三 null
..... ..... 张三 null

[Yole]

create  table #a
(  
    pf_id int,
    pf_name varchar(20)
)

insert into #a
select  1, 'QQ音乐' union all
select     3, '网易云音乐' union all
select      6, '微博' union all
 select     7, '抖音' union all
 select    8, '酷我音乐' union all
select    9, '酷狗' union all
select     13, '虾米音乐' union all
 select    14, '快手' union all
select     18, 'B站' union all
select    19, '小红书'

create  table #b (
     
    pf_id int,
    pf_name varchar(20),
    person varchar(100),
    hot_heat int 
)

insert  into #b
select 1,'QQ音乐','张三','100'


select a.pf_id,a.pf_name,b.person ,b.hot_heat  from #a  a left join #b b on a.pf_id=b.pf_id

/*
pf_id       pf_name              person                                                                                               hot_heat
----------- -------------------- ---------------------------------------------------------------------------------------------------- -----------
1           QQ音乐                 张三                                                                                                   100
3           网易云音乐                NULL                                                                                                 NULL
6           微博                   NULL                                                                                                 NULL
7           抖音                   NULL                                                                                                 NULL
8           酷我音乐                 NULL                                                                                                 NULL
9           酷狗                   NULL                                                                                                 NULL
13          虾米音乐                 NULL                                                                                                 NULL
14          快手                   NULL                                                                                                 NULL
18          B站                   NULL                                                                                                 NULL
19          小红书                  NULL                                                                                                 NULL

(10 行受影响)
*/

[RINK_1]

SELECT B.PF_ID,B.PF_NAME,A.PERSON,C.HOT_HEAT
FROM 
(SELECT DISTINCT PERSON FROM artist_platform_info) AS A
JOIN all_platform_info B ON 1=1
LEFT JOIN
(SELECT PF_ID,PERSON,SUM(HOT_HEAT) AS HOT_HEAT
FROM artist_platform_info 
GROUP BY PF_ID,PERSON) AS C ON A.PERSON=C.PERSON AND B.PF_ID=C.PF_ID