mysql 笔试题_MySQL笔试题详解（一）(中等难度)

有一位学生在找数据分析工作的时候，遇到一个笔试题，内容如下：

现有注册用户表table_user，有两个字段：user_id(用户id)、reg_tm(注册时间)。有订单表table_order,有三个字段：order_id(订单号)、order_tm(下单时间)、user_id(用户id)。

查询2019年1月1日至今，每天的注册用户数，下单用户数，以及注册当天即下单的用户数(请尽量在一个 sql语句中实现)。

题意分析：

1.要查看的是每一天的情况，所以要以日期为维度进行汇总观测，也就是group by后面跟日期字段；

2.要看每天的注册用户数(来自用户表)，每天的下单用户数(来自订单表)，所以要将用户表和订单表做横向连接；

3.两表连接的字段是什么？第一感觉应该是user_id，但是我们通过user_id字段连接两表后，两表都有时间字段，那以哪个字段为分组依据呢？比如用户「小包总」在6月10日注册了网站，在6月20日下了第一笔订单，以user_id字段连接两表，一个user_id对应两个时间，以注册时间为分组依据，得不到准确的当日下单用户数，以下单时间为分组依据，得不到准确的当日注册用户数；

4.不能用user_id做连接字段，需要用用户表的注册时间和订单表的下单时间作为连接字段。如果两个表的时间范围保持一致，那直接做表连接没有问题，但如果时间范围不一致，比如用户表在6月20日没有注册量，在订单表6月20日有多笔订单，用户注册表在6月10日有多位用户注册，而订单表6月10日没有订单。而在MySQL里面只有左连、右连、内连三种连接方式，不管以何种方式做连接，总会丢失部分日期记录；

5.只有外连才会不丢失日期数据，而MySQL里面没有外连方式，这要怎么办？我们可以通过union纵向链接的方式构造外连一样的结果；

综合以上分析，得到解题思路：

1.将注册表的注册时间和订单表的下单时间做纵向链接，生成一个临时表，只有一个字段 reg_tm：

select reg_tm from table_user

union

select order_tm from table_order;

2.再用上表和注册表及订单表做左连接：

select * from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm;

3.题目要求查询2019年1月1日至今的数据情况，把这个条件加在where后面：

select * from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm

where table_date.reg_tm>="2019-01-01";

4.题目是查看每天的注册用户数，下单用户数，以及注册当天即下单的用户数；需要对日期进行分组，注册用户数是对注册表的user_id进行计数，下单用户数是对订单表的user_id进行计数，注册当天即下单的用户数是对注册表的注册时间与订单表的注册时间相等的user_id进行计数。需要注意的是，在将临时表table_date与table_user左连时，对应关系是一对多，生成的结果是一个多表，再与table_order左连，对应关系是多对多，多对多的情况下，数据一定是有重复的，所以需要去重处理(distinct函数)。另外把没有结果的null替换成0(ifnull函数)，最终代码如下：

select table_date.reg_tm,ifnull(count(distinct table_user.user_id),0) 注册用户数,ifnull(count(distinct table_order.user_id),0) 下单用户数,ifnull(count(distinct if(table_user.reg_time=table_order.order_time and table_user.user_id=table_order.user_id,table_user.user_id,null)),0) 下单用户数

from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm

where table_date.reg_tm>="2019-01-01"

group by table_date.reg_tm;

题目是没有数据的，如果直接看看不懂的话，可以自己先构造一个数据，再尝试文中的代码，一步一步理解。

内容较多，可以先码后看，如果对你有帮助，给个赞和关注哈~，如有其它解题思路，欢迎交流~