sql使用中的一些固定语法

#sql求分组后 组内排名前几--每个uid在9月份登录的前七天，与后7天（分别计算，不足7天的取全部）

SELECT * from ( SELECT  
 uid,
 day,
 @ROW := case when @cid=uid then @ROW+1 else 1 END rn, 
 @cid := uid
from 
(
　　SELECT uid,day  from 
　　ods_app_action_login_h 
　　where day>="2019-09-01" and day <="2019-09-30" and uid <>''  
      GROUP BY day,uid ORDER BY uid,day desc
) l1,(SELECT @ROW := 0,@cid :=0) l2) l5
where rn <=7

--第二种
SELECT uid,day 
from ods_app_action_login_h as o1 
where day>="2019-09-27" and day<="2019-09-30" and 
　(
　　　　SELECT count(*) from 
　　　　(
　　　　　 　SELECT uid,day from ods_app_action_login_h where 
    　　day>="2019-09-27" and day<="2019-09-30" GROUP BY uid,day
　　    ) as o 
    where  o1.uid= o.uid and o.day <=o1.day   --分组排序
　) <= 7 

 #从上一结果中筛选出isguest<>1的uid(当天只要有一条数据isguest=1,则此uid视为isguest=1,前7天后7天可任选一个)

SELECT * from 
(
    SELECT 
     uid,
     day,
     @ROW := case when @cid=uid then @ROW+1 else 1 END rn,
     @cid := uid
    from 
    (
        SELECT uid,day  from 
        ods_app_action_login_h 
        where day>="2019-09-01" and day <="2019-09-30" and uid <>''  GROUP BY day,uid ORDER BY uid,day desc
    ) l1,(SELECT @ROW := 0,@cid :=0) l2
) l5
INNER JOIN(
    SELECT uid,day from (        
       SELECT uid,sum(if(isguest!=1,0,1)) is_isguest,day from ods_app_action_login_h 
        where day>="2019-09-01" and day <="2019-09-30" GROUP BY uid,day
            ) s1 where s1.is_isguest=0
) l6 on l5.uid = l6.uid
where rn <=7


#下面效率不高

-- SELECT d1.uid,d1.isguest,d1.day from ods_app_action_login_h as d1
-- LEFT JOIN
-- (
-- SELECT * from (
-- SELECT uid,isguest,day from
-- ods_app_action_login_h
-- where day>="2019-09-29" and day <="2019-09-30" and uid <>'' GROUP BY uid,isguest,day) as o3 WHERE isguest !=1
-- ) l1 on l1.uid = d1.uid and l1.day =d1.day
-- where d1.day>="2019-09-29" and d1.day <="2019-09-30" GROUP BY uid
--