题解 | 查询单日多次下订单的用户信息？

/*
日期+用户分组，统计数量
会员信息在前面的查询中用不到，可以放在最外层匹配
*/

select day_table.order_date	,day_table.user_id,day_table.order_nums,u.vip

from (
select  DATE_FORMAT(order_time,'%Y-%m-%d')  as order_date,user_id,count(*) as order_nums
from order_tb
group by DATE_FORMAT(order_time,'%Y-%m-%d'),user_id
) as day_table

join uservip_tb u
on day_table.user_id = u.user_id

where day_table.order_nums> 1
order by day_table.order_nums desc

SELECT

DATE(ot.order_time) AS order_date,

ot.user_id,

COUNT(ot.user_id) AS order_nums

FROM

order_tb ot

GROUP BY

DATE(ot.order_time), ot.user_id

HAVING

COUNT(ot.user_id) > 1

ORDER BY

order_date ASC,

order_nums DESC;

和标准答案想比，有一定的优势，但是在代码书写上面显得还是复杂

比如在日期提取上面，是通过date_rotmat()转换格式，但实际上可以通过DATE()来提取

再有还是要牢记count函数是统计条数，不是统计distinct 去重之后的数量

再有优化的话，直接在子查询内group 之后having一下，进一步缩小一下子查询的表

算是第一个我能够独立完成的sql，非常开心