SQL题详解：查询每个餐厅最近一个月内经常来消费的顾客前三名，需要查询出顾客的名字

第一步：先查出每个餐厅最近一个月顾客的总消费次数

select restaurant_id,user_id, count(*) sum from orders 
       where order_date<=(select max(order_date) from orders)
       and order_date>=add_months((select max(order_date) from orders),-1) group by restaurant_id,user_id;

在orders表中，用group by根据餐厅(restaurant_id)和用户(user_id)查出每个餐厅顾客的总消费次数。在查询最近一个月的日期时，需要先找出最近一次消费的日期，这里我主要使用函数max()求出最近一天的日期，然后利用这个日期通过add_months()函数求出一个月之前的日期。

这里，也可以使用开窗函数count(*) over(),用partition by进行分组查询

select restaurant_id,user_id,count(*) over(partition by restaurant_id,user_id) from orders where order_date<=(select max(order_date) from orders)
                                            and order_date>=add_months((select max(order_date) from orders),-1);

第二步：用刚查询出来的结果作为一个临时表，进行子查询，对顾客的消费次数进行倒序排序

select *,dense_rank() over (partition by restaurant_id order by sum desc ) num from (
     select restaurant_id,user_id, count(*) sum from orders where order_date<=(select max(order_date) from orders)
                                            and order_date>=add_months((select max(order_date) from orders),-1)
       group by restaurant_id,user_id
                                                    ) a;

对临时表中的顾客消费次数用dense_rank()进行排序，在出现相同次数时，并列排序（row_number()依次排序，不会进行并列排序）。

注意：在用临时表做子查询时，记得给临时表起别名，否则会报如下错误：

第三步：由于对次数排完序后，不能在where中用别名num对num<=3的情况进行截取，所以此处再次使用子查询

select * from (
    select *,dense_rank() over (partition by restaurant_id order by sum desc ) num from(
    select restaurant_id,user_id, count(*) sum from orders where order_date<=(select max(order_date) from orders)
                                            and order_date>=add_months((select max(order_date) from orders),-1)
       group by restaurant_id,user_id
                                                    ) a
              ) a1 where num<=3;
             

第四步：多表关联，通过两个表中共有的user_id进行关联，查询出对应顾客的姓名

select  restaurant_id,user_name,sum from users u,(
    select * from (
    select *,dense_rank() over (partition by restaurant_id order by sum desc ) num from(
    select restaurant_id,user_id, count(*) sum from orders where order_date<=(select max(order_date) from orders)
                                            and order_date>=add_months((select max(order_date) from orders),-1)
       group by restaurant_id,user_id
                                                    ) a
              ) a1 where num<=3
) o where o.user_id=u.user_id;

在进行多表关联时，有多种方案：

例：

(1)方言式关联(对应的是标准sql中的内连接)

select * from emp e,dept d where e.deptno=d.deptno;

select * from emp e inner join dept d on e.deptno = d.deptno;

 

 (2)左连接：以坐标为主，坐标的数据全部显示，能关联上右表的显示，关联不上的显示为null

select * from emp e left join dept d on e.deptno=d.deptno;

(3)右连接：以右表为主，右表的数据全部显示，能关联左表的显示，关联不上的，显示为null

select * from emp right outer join dept on emp.deptno = dept.deptno;

 

 注意：多表关联时，如果一个字段非常的明确，可以不在前面加表名，如果在多个表中都有该字段，必须在字段前面指明是哪个表的字段，否则会报错！！！