大数据第56天—Mysql练习题12道之十一-查出销售表中所有会员购买金额,同时分组查出退货表中所有会员的退货金额-杨大伟
-- 1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）
-- （1）会员表有字段memberid（会员id,主键）credits（积分）；
-- （2）销售表有字段memberid（会员id,外键）购买金额（MNAccount）；
-- （3）退货表中有字段memberid（会员id,外键）退货金额（RMNAccount）；
-- 2）业务说明：
-- （1）销售表中的销售记录可以是会员购买,也可是非会员购买。（即销售表中的memberid可以为空）
-- （2）销售表中的一个会员可以有多条购买记录
-- （3）退货表中的退货记录可以是会员,也可是非会员4、一个会员可以有一条或多条退货记录
-- 查询需求：分组查出销售表中所有会员购买金额,同时分组查出退货表中所有会员的退货金额,
-- 把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits）
1 -- 建表 2 --会员表 3 drop table if exists test_eleven_member; 4 create table test_eleven_member( 5 memberid string COMMENT '会员id', 6 credits bigint COMMENT '积分' 7 ) 8 row format delimited fields terminated by '\t'; 9 --销售表 10 drop table if exists test_eleven_sale; 11 create table test_eleven_sale( 12 memberid string COMMENT '会员id', 13 MNAccount decimal(10,2) COMMENT '购买金额' 14 ) 15 row format delimited fields terminated by '\t'; 16 --退货表 17 drop table if exists test_eleven_regoods; 18 create table test_eleven_regoods( 19 memberid string COMMENT '会员id', 20 RMNAccount decimal(10,2) COMMENT '退货金额' 21 ) 22 row format delimited fields terminated by '\t';
1 insert into table test_eleven_member values('1001',0); 2 insert into table test_eleven_member values('1002',0); 3 insert into table test_eleven_member values('1003',0); 4 insert into table test_eleven_member values('1004',0); 5 insert into table test_eleven_member values('1005',0); 6 insert into table test_eleven_member values('1006',0); 7 insert into table test_eleven_member values('1007',0); 8 9 insert into table test_eleven_sale values('1001',5000); 10 insert into table test_eleven_sale values('1002',4000); 11 insert into table test_eleven_sale values('1003',5000); 12 insert into table test_eleven_sale values('1004',6000); 13 insert into table test_eleven_sale values('1005',7000); 14 insert into table test_eleven_sale values('1004',3000); 15 insert into table test_eleven_sale values('1002',6000); 16 insert into table test_eleven_sale values('1001',2000); 17 insert into table test_eleven_sale values('1004',3000); 18 insert into table test_eleven_sale values('1006',3000); 19 insert into table test_eleven_sale values(NULL,1000); 20 insert into table test_eleven_sale values(NULL,1000); 21 insert into table test_eleven_sale values(NULL,1000); 22 insert into table test_eleven_sale values(NULL,1000); 23 24 insert into table test_eleven_regoods values('1001',1000); 25 insert into table test_eleven_regoods values('1002',1000); 26 insert into table test_eleven_regoods values('1003',1000); 27 insert into table test_eleven_regoods values('1004',1000); 28 insert into table test_eleven_regoods values('1005',1000); 29 insert into table test_eleven_regoods values('1002',1000); 30 insert into table test_eleven_regoods values('1001',1000); 31 insert into table test_eleven_regoods values('1003',1000); 32 insert into table test_eleven_regoods values('1002',1000); 33 insert into table test_eleven_regoods values('1005',1000); 34 insert into table test_eleven_regoods values(NULL,1000); 35 insert into table test_eleven_regoods values(NULL,1000); 36 insert into table test_eleven_regoods values(NULL,1000); 37 insert into table test_eleven_regoods values(NULL,1000);
1 -- 分组查出销售表中所有会员购买金额,同时分组查出退货表中所有会员的退货金额, 2 -- 把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits） 3 with 4 tmp_member as 5 ( 6 select memberid,sum(credits) credits 7 from test_eleven_member 8 group by memberid 9 ), 10 tmp_sale as 11 ( 12 select memberid,sum(MNAccount) MNAccount 13 from test_eleven_sale 14 group by memberid 15 ), 16 tmp_regoods as 17 ( 18 select memberid,sum(RMNAccount) RMNAccount 19 from test_eleven_regoods 20 group by memberid 21 ) 22 insert overwrite table test_eleven_member 23 select 24 t1.memberid, 25 sum(t1.creadits)+sum(t1.MNAccount)-sum(t1.RMNAccount) credits 26 from 27 ( 28 select 29 memberid, 30 credits, 31 0 MNAccount, 32 0 RMNAccount 33 from tmp_member 34 union all 35 select 36 memberid, 37 0 credits, 38 MNAccount, 39 0 RMNAccount 40 from tmp_sale 41 union all 42 select 43 memberid, 44 0 credits, 45 0 MNAccount, 46 RMNAccount 47 from tmp_regoods 48 ) t1 49 where t1.memberid is not NULL 50 group by t1.memberid
1 ---------------------第2种写法-用left join-------------------------- 2 insert overwrite table test_eleven_member 3 select 4 t3.memberid, 5 sum(t3.credits) credits 6 from 7 ( 8 select 9 t1.memberid, 10 t1.MNAccount - NVL(t2.RMNAccount,0) credits 11 from 12 ( 13 select 14 memberid, 15 sum(MNAccount) MNAccount 16 from test_eleven_sale 17 group by memberid 18 ) t1 19 left join 20 ( 21 select 22 memberid, 23 sum(RMNAccount) RMNAccount 24 from test_eleven_regoods 25 group by memberid 26 )t2 27 on t1.memberid = t2.memberid 28 where t1.memberid is not NULL 29 30 union all 31 32 select 33 memberid, 34 credits 35 from test_eleven_member 36 ) t3 37 group by t3.memberid;
