回忆是一座桥
却是通往寂寞的牢

SQL案例 - 查询下订单用户访问次数

描述

现有某个商城部分订单数据,用户访问数据,如下所示:

订单信息表:order_tb(订单id-order_id,用户id-user_id,订单金额-order_price,订单创建时间-order_time)

order_id user_id order_price order_time
101 11 380 2022-09-01 09:00:00
102 12 200 2022-09-01 10:00:00
103 13 260 2022-09-01 12:00:00
104 11 100 2022-09-02 11:00:00
105 12 150 2022-09-02 12:00:00
106 12 1200 2022-09-02 13:00:00
107 11 60 2022-09-03 09:00:00
108 13 380 2022-09-03 09:30:00

访问信息表:visit_tb(访问信息id-info_id,用户id-user_id,访问时间-visit_time,离开时间-leave_time)

info_id user_id visit_time leave_time
911 10 2022-09-01 08:00:00 2022-09-01 09:02:00
912 11 2022-09-01 08:30:00 2022-09-01 09:10:00
913 12 2022-09-01 09:50:00 2022-09-01 10:12:00
914 13 2022-09-01 11:40:00 2022-09-01 12:22:00
921 11 2022-09-02 10:30:00 2022-09-02 11:05:00
922 11 2022-09-02 12:00:00 2022-09-02 12:02:00
923 12 2022-09-02 11:40:00 2022-09-02 13:15:00
924 13 2022-09-02 09:00:00 2022-09-02 09:02:00
925 14 2022-09-02 10:00:00 2022-09-02 10:40:00
931 10 2022-09-03 09:00:00 2022-09-03 09:22:00
932 11 2022-09-03 08:30:00 2022-09-03 09:10:00
933 13 2022-09-03 09:00:00 2022-09-03 09:32:00

请查询2022年9月2日这天下订单的用户的访问次数?
要求输出:user_id,访问次数
注:访问次数定义为用户在该日"访问-离开"则记录1次,输出结果按照访问次数降序排序

示例数据结果如下:

user_id visit_nums
11 2
12 1

结果解释:

2022年9月2日这天user_id为11、12的用户下了订单,

其中11用户分别在10:30-11:05及12:00-12:02访问,故访问次数为2次,

12用户在11:40-13:15访问,故访问次数为1次。

示例1

输入:

drop table if exists  `order_tb` ; 
CREATE TABLE `order_tb` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`order_price` int(11) NOT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_id`));
INSERT INTO order_tb VALUES(101,11,380,'2022-09-01 09:00:00'); 
INSERT INTO order_tb VALUES(102,12,200,'2022-09-01 10:00:00'); 
INSERT INTO order_tb VALUES(103,13,260,'2022-09-01 12:00:00'); 
INSERT INTO order_tb VALUES(104,11,100,'2022-09-02 11:00:00'); 
INSERT INTO order_tb VALUES(105,12,150,'2022-09-02 12:00:00'); 
INSERT INTO order_tb VALUES(106,12,1200,'2022-09-02 13:00:00'); 
INSERT INTO order_tb VALUES(107,11,60,'2022-09-03 09:00:00'); 
INSERT INTO order_tb VALUES(108,13,380,'2022-09-03 09:30:00'); 

drop table if exists  `visit_tb` ; 
CREATE TABLE `visit_tb` (
`info_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`visit_time` datetime NOT NULL,
`leave_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO visit_tb VALUES(0911,10,'2022-09-01 08:00:00','2022-09-01 09:02:00'); 
INSERT INTO visit_tb VALUES(0912,11,'2022-09-01 08:30:00','2022-09-01 09:10:00'); 
INSERT INTO visit_tb VALUES(0913,12,'2022-09-01 09:50:00','2022-09-01 10:12:00'); 
INSERT INTO visit_tb VALUES(0914,13,'2022-09-01 11:40:00','2022-09-01 12:22:00'); 
INSERT INTO visit_tb VALUES(0921,11,'2022-09-02 10:30:00','2022-09-02 11:05:00'); 
INSERT INTO visit_tb VALUES(0922,11,'2022-09-02 12:00:00','2022-09-02 12:02:00'); 
INSERT INTO visit_tb VALUES(0923,12,'2022-09-02 11:40:00','2022-09-02 13:15:00'); 
INSERT INTO visit_tb VALUES(0924,13,'2022-09-02 09:00:00','2022-09-02 09:02:00'); 
INSERT INTO visit_tb VALUES(0925,14,'2022-09-02 10:00:00','2022-09-02 10:40:00'); 
INSERT INTO visit_tb VALUES(0931,10,'2022-09-03 09:00:00','2022-09-03 09:22:00'); 
INSERT INTO visit_tb VALUES(0932,11,'2022-09-03 08:30:00','2022-09-03 09:10:00'); 
INSERT INTO visit_tb VALUES(0933,13,'2022-09-03 09:00:00','2022-09-03 09:32:00'); 

输出:

user_id|visit_nums
11|2
12|1

答案

解法1:

select
    user_id,
    count(info_id) visit_nums
from
    visit_tb
where
    user_id in (
        select user_id from order_tb where date(order_time)='2022-09-02'
    )
    and date(visit_time)='2022-09-02'
group by
    user_id
order by visit_nums desc;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 查询下订单用户访问次数
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论