题目：

有如下一张合同表T image 现在想获得每个直接合同的价格已经对应的补充合同的价格，得到的结果大致如下： image

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200520
(
    id       int,
    type     varchar(20),
    masterid int,
    amount   int
);

数据准备

insert into dailytest_20200520 values(1,'直接合同',null,5000);
insert into dailytest_20200520 values(2,'补充合同',1,1000);
insert into dailytest_20200520 values(3,'补充合同',1,500);
insert into dailytest_20200520 values(4,'直接合同',null,6000);
insert into dailytest_20200520 values(5,'直接合同',null,4000);
insert into dailytest_20200520 values(6,'补充合同',5,1000);

查询逻辑

select
       A.id,
       A.type,
       IFNULL(A.amount, 0) AS Amount1,
       IFNULL(B.amount, 0) AS Amount2
from (
    select
           id,
           type,
           amount
    from dailytest_20200520 where masterid is null) A
         left join
     (select distinct
             type,
             masterid,
             sum(amount) over (partition by masterid) as amount
      from dailytest_20200520 where masterid is not null) B
    ON A.id = B.masterid;

附：
题目来源：https://mp.weixin.qq.com/s/cOaSDCBl5ksQsnY1Z2TD5Q