求助数据库笔试题目,打扰了^^

#MySQL##SQL Server#
全部评论
可以来这里多练习一下 https://www.nowcoder.com/ta/sql
1 回复
分享
发布于 2019-05-22 17:35
--第一题 select coalesce(a.create_date, b.create_date) as static_date     , coalesce(a.new_user_cnt, 0) as new_user_cnt     , coalesce(b.order_cnt, 0) as order_cnt     , coalesce(b.order_user_cnt, 0) as order_user_cnt from (     select substr(create_time, 1, 10) as create_date         , count(user_id) as new_user_cnt     from user     where 1 = 1     group by substr(create_time, 1, 10) ) a  full join (     select substr(create_time, 1, 10) as create_date         , count(distinct user_id) as order_user_cnt         , count(order_id) as order_cnt     from order     where 1 = 1     group by substr(create_time, 1, 10) ) b on a.create_date = b.create_date ; --第二题 select city_id     , count(user_id) as order_user_cnt     , count(if(to_date(first_order_date) between between date_sub(current_date, 7) and date_sub(current_date, 1), user_id, null)) as first_order_user_cnt from (     select user_id         , city_id     from order     where to_date(substr(create_time, 1, 10)) between date_sub(current_date, 7) and date_sub(current_date, 1)     group by user_id         , city_id ) a join (     select user_id         , city_id         , substr(min(create_time), 1, 10) as first_order_date     from order     where 1 = 1     group by user_id         , city_id ) b on a.user_id = b.user_id     and a.city_id = b.city_id where 1 = 1  group by city_id ;
点赞 回复
分享
发布于 2019-05-22 17:23
乐元素
校招火热招聘中
官网直投

相关推荐

点赞 3 评论
分享
牛客网
牛客企业服务