好久都没有花这么长时间找一个bug了，而且还是关于SQL代码的bug，
1 问题场景引入
目前人们的日常生活消费无非有两种最常用的方式：手机移动支付和银行卡转账。
假设我们有一个需求，就是根据不同用户统计出他在银行卡端特定的消费类型和手机移动端的支付次数之和。
我们模拟银行卡端和手机移动端的最简单状况下的数据表结构：
- 银行卡端：用户ID(user_id)、用户名(user_name)、银行卡号(card_id)、消费金额(money)、消费标识(level)
- 手机端：用户ID(user_id)、用户名(user_name)、消费金额(money)
下面我们根据需求建表：
CREATE TABLE `tb_bank` ( `user_id` int(11) NOT NULL, `user_name` varchar(255) NOT NULL, `card_id` int(11) NOT NULL, `money` double NOT NULL, `level` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `tb_phone` ( `user_id` int(11) NOT NULL, `user_name` varchar(255) NOT NULL, `money` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
为了能更好的演示，我们提前放好数据：
-- 银行卡端消费记录 insert into tb_bank(user_id,user_name,card_id,money,level) values(1,"zs",1001,10.0,1); insert into tb_bank(user_id,user_name,card_id,money,level) values(1,"zs",1002,10.0,2); insert into tb_bank(user_id,user_name,card_id,money,level) values(1,"zs",1003,10.0,3); insert into tb_bank(user_id,user_name,card_id,money,level) values(1,"zs",1004,10.0,1); insert into tb_bank(user_id,user_name,card_id,money,level) values(2,"ls",1001,10.0,1); insert into tb_bank(user_id,user_name,card_id,money,level) values(2,"ls",1002,10.0,3); insert into tb_bank(user_id,user_name,card_id,money,level) values(3,"ww",1001,10.0,1); insert into tb_bank(user_id,user_name,card_id,money,level) values(3,"ww",1001,10.0,2); insert into tb_bank(user_id,user_name,card_id,money,level) values(3,"ww",1002,10.0,1); insert into tb_bank(user_id,user_name,card_id,money,level) values(4,"zl",1001,10.0,1); -- 手机端消费记录 insert into tb_phone(user_id,user_name,money) values(1,"zs",10.0); insert into tb_phone(user_id,user_name,money) values(1,"zs",10.0); insert into tb_phone(user_id,user_name,money) values(2,"ls",15.0); insert into tb_phone(user_id,user_name,money) values(2,"ls",1.0); insert into tb_phone(user_id,user_name,money) values(3,"ww",10.0); insert into tb_phone(user_id,user_name,money) values(3,"ww",13.0); insert into tb_phone(user_id,user_name,money) values(3,"ww",12.0); insert into tb_phone(user_id,user_name,money) values(3,"ww",100.0); insert into tb_phone(user_id,user_name,money) values(4,"zl",120.0); insert into tb_phone(user_id,user_name,money) values(4,"zl",130.0);
我们看下最终的数据：
解释下这个些数据的初步含义：
2 具体需求引入
现在有一个需要我们进行数据分析的实际需求：
查询用户在手机端的消费记录总数和用户使用银行卡端的第一（1001）、二（1002）张银行卡（如果没有第二张只算第一张）以及level为1和2时的消费记录总数之和。
分析需求：
- 手机端消费记录总数。这个只需要根据user_id和user_name直接group by就行了。
- 银行卡端第一、第二张并且level为1或2。这个需求看起来比较麻烦，但是仔细分析，无非就是限制两个条件并且利用and连接。
3 需求解决过程
根据上一节的需求引入和分析，我们不难分析出SQL的设计
- （1）查询出手机端记录的总数，根据user_name和user_id进行group by
- （2）查询出加以条件的银行卡端记录的总数，根据user_name、user_id、card_id和level进行group by
- （3）连表查询，计算出相同user_id和user_name的消费记录总和
由此，我们写出对应的SQL
with bank_base as ( select user_id, user_name, card_id, level, count(*) as cnt from tb_bank where level in (1, 2) and card_id in (1001, 1002) group by user_id, user_name, card_id, level ), phone_base as ( select user_id, user_name, count(*) as cnt from tb_phone group by user_id, user_name ), ln as ( select b.user_id as user_id, b.user_name as user_name, sum(b.cnt) + sum(p.cnt) as cunsum_num from bank_base as b LEFT JOIN phone_base as p on (b.user_id = p.user_id and b.user_name = p.user_name) group by user_id, user_name), rn as ( select b.user_id as user_id, b.user_name as user_name, sum(b.cnt) + sum(p.cnt) as cunsum_num from bank_base as b right JOIN phone_base as p on (b.user_id = p.user_id and b.user_name = p.user_name) group by user_id, user_name) SELECT * from (select * from rn UNION select * FROM ln) a;
查询结果：
注意点:
- MySQL版本8.0之后才支持
with ...as ...语句 - 在多表查询中分为left join、right join、full outer join、inner join、outer join等等，如下图
但是MySQL中并没有支持full join相关关键字，所以我们只能使用先左连接再右连接最后再将结果union的方式对full outer join进行替代。即
左关联结果表： tempa 右关联结果表： tempb 全关联结果表： select * from ( select * from tempa union select * from tempb) c;
4 重点来啦
我们看下结果，乍一看SQL语句的运行没有出错，但是稍微仔细一看我们就看出来了，因为两个表里只有十条数据
我们只需要一双手就能数出来用户ww的银行卡消费记录和手机消费记录总共在7条，而结果却成了15条
如果不信，我们来分步骤测试下：
- 首先看下银行卡端的SQL：
select user_id, user_name, card_id, level, count(*) as 消费总数 from tb_bank where level in (1, 2) and card_id in (1001, 1002) group by user_id, user_name, card_id, level
- 结果：
- 再看下手机端的SQL：
select user_id, user_name, count(*) as 消费总数 from tb_phone group by user_id, user_name
- 结果：
得出结论，我们写的SQL确实有问题！
5 问题解决过程
有问题就需要解决。其实如果对SQL有丰富经验的话在第四步就能看出来问题出现在哪了
对，两表连接查询on … group by …的时候
图示：
根据产生问题的原因，我们进行规避后再进行查询，上图问题的避免方式：
- A表只group by user_name和user_id这两个字段
- 或者B表也增加level和card_id字段并固定这两个字段的值，从而达到只连接一次的效果
最终SQL:
with bank_base as ( select user_id, user_name,count(*) as cnt from tb_bank where level in (1, 2) and card_id in (1001, 1002) group by user_id, user_name ), phone_base as ( select user_id, user_name, count(*) as cnt from tb_phone group by user_id, user_name ), ln as ( select b.user_id as user_id, b.user_name as user_name, sum(b.cnt) + sum(p.cnt) as cunsum_num from bank_base as b LEFT JOIN phone_base as p on (b.user_id = p.user_id and b.user_name = p.user_name) group by user_id, user_name), rn as ( select b.user_id as user_id, b.user_name as user_name, sum(b.cnt) + sum(p.cnt) as cunsum_num from bank_base as b right JOIN phone_base as p on (b.user_id = p.user_id and b.user_name = p.user_name) group by user_id, user_name) SELECT * from (select * from rn UNION select * FROM ln) a;
因为发现tb_bank表group by card_id, level的结果我们在下次查询中用不到，所以只group by user_id, user_name
最终结果：
6 小总结
- （1）在两表联查时，我们尽量要看清on和group的字段，到底符不符合我们的最终数据要求，两表的匹配和连接过程是怎样的，会不会导致多次联查等等（本次解决问题的重点）。
- （2）将SQL联表查询的关键字如inner join、full outer join、left join等等的查询范围熟记于心，特别是inner join和full outer join。
- inner join：多用于两表相互筛选查询，也就是符合两个表的特定字段都共有的数据的条目。
- full outer join：多用于两表互补查询，将两个表对方都没有的数据进行互补，并针对on的字段进行合并。
- （3）学会对结果进行验证，对问题的验证和排查，并能够在SQL中进行debug，虽然SQL不像业务代码一样有好用的IDE，但是仍然能对SQL进行数据的debug，比如问题确定过程中的将SQL摘出来进行单独的查询，把结果进行校验。
- （4）最重要的还是要认真。
