原始数据

# 创建习题数据库
create database test;
# 使用习题数据库
use test;
# 创建理财单表
create table icd
(
l_id char(7) comment '理财单id',
u_id char(5) comment '用户id',
amount int(6) comment '购买金额(元)',
product char(5) comment '购买产品',
platform varchar(3) comment '购买平台',
buy_date date comment '购买日期'
)
char set utf8;
# 创建邀请关系表
create table invite_user
(
rela_id char(5) comment '邀请关系id',
inv_id char(5) comment '邀请人',
invd_id char(5) comment '被邀请人',
start_date date comment '创建日期',
del char(1) comment '是否删除'
)
char set utf8;
# 创建债券转让表
create table transfer
(
tra_id char(7) comment '债权转让id',
person_id char(5) comment '用户id',
trans_amount int(6) comment '债权转让购买金额',
trans_date date comment '购买日期'
)
char set utf8;
# 理财单表插值
insert icd values
('LCD0001', 'U0002', 5000, 'P0001', 'PC', '2016/2/1'),
('LCD0002', 'U0002', 10000, 'P0001', 'APP', '2016/2/3'),
('LCD0003', 'U0005', 2000, 'P0001', '微信', '2016/2/4'),
('LCD0004', 'U0006', 900, 'P0002', 'APP', '2016/2/14'),
('LCD0005', 'U0004', 10000, 'P0001', 'PC', '2016/2/28'),
('LCD0006', 'U0003', 3000, 'P0001', 'APP', '2016/3/1'),
('LCD0007', 'U0007', 5000, 'P0002', 'APP', '2016/3/4'),
('LCD0008', 'U0008', 5000, 'P0002', 'PC', '2016/3/6'),
('LCD0009', 'U0010', 10000, 'P0001', 'APP', '2016/3/5'),
('LCD0010', 'U0010', 30000, 'P0001', 'APP', '2016/3/5'),
('LCD0011', 'U0011', 1500, 'P0002', 'APP', '2016/3/5'),
('LCD0012', 'U0001', 100, 'P0001', 'APP', '2016/3/12'),
('LCD0013', 'U0013', 200, 'P0003', 'PC', '2016/3/23'),
('LCD0014', 'U0009', 10000, 'P0001', 'APP', '2016/4/1'),
('LCD0015', 'U0009', 100000, 'P0002', 'PC', '2016/4/2'),
('LCD0016', 'U0016', 20000, 'P0001', '微信', '2016/4/5'),
('LCD0017', 'U0001', 4500, 'P0001', '微信', '2016/4/10'),
('LCD0018', 'U0020', 900, 'P0002', 'PC', '2016/4/11'),
('LCD0019', 'U0022', 1100, 'P0002', 'APP', '2016/4/11'),
('LCD0020', 'U0028', 2000, 'P0002', 'APP', '2016/4/11');
# 邀请关系表插值
insert invite_user values
('I0001', 'U0002', 'U0003', '2015/2/1', 'N'),
('I0002', 'U0002', 'U0004', '2015/3/1', 'N'),
('I0003', 'U0005', 'U0010', '2015/3/3', 'Y'),
('I0004', 'U0007', 'U0010', '2015/3/13', 'N'),
('I0005', 'U0001', 'U0011', '2015/4/1', 'N'),
('I0006', 'U0007', 'U0014', '2015/3/26', 'N'),
('I0007', 'U0013', 'U0016', '2015/4/12', 'N'),
('I0008', 'U0013', 'U0020', '2015/4/12', 'N'),
('I0009', 'U0013', 'U0022', '2015/4/16', 'N'),
('I0010', 'U0005', 'U0026', '2015/5/2', 'N');
# 债券转让表插值
insert transfer values
('TRA0001', 'U0022', '5000', '2016/2/1'),
('TRA0002', 'U0023', '9000', '2016/2/3'),
('TRA0003', 'U0026', '2000', '2016/4/4'),
('TRA0004', 'U0024', '1100', '2016/3/4'),
('TRA0005', 'U0024', '7000', '2016/2/28'),
('TRA0006', 'U0027', '3000', '2016/3/1'),
('TRA0007', 'U0030', '5000', '2016/3/7'),
('TRA0008', 'U0032', '4000', '2016/3/26'),
('TRA0009', 'U0036', '2400', '2016/4/8'),
('TRA0010', 'U0031', '3000', '2016/4/15');

查询练习

1. 找出邀请 2 个及以上的邀请人, 计算每个邀请人对应的成功邀请人数和被邀请人的购买金额(不含债转)

select a.inv_id, a.num, b.invd_id, c.amount from
(
select inv_id,count(distinct invd_id) as num from invite_user
where del='N'
group by inv_id
having num>=2
) as a
left join
(
select inv_id,invd_id from invite_user
) as b
on a.inv_id=b.inv_id
left join
(
select u_id,amount from icd
) as c
on b.invd_id=c.u_id;