SQLSERVER 如何快速获取每天最后10笔订单

Question

现有表结构 t_order，里面有2个字段
orderid 订单编号 int 自增长主键
userid 用户编号 int
createtime 创建时间 datetime

每天大概30万条订单。如何快速获取近10天，每天最后5笔订单，不想用游标，

列出最近5天内有订单的用户，并列出最后5笔订单信息

Answer 1

----获取近10天，每天最后5笔订单（createtime的长度为10,不是的需要转换）
--分组倒排订单
WITH _A AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid 
    FROM t_order 
    WHERE datediff(d, createtime, getdate()) <= 10
)
--SELECT num,orderid FROM _A b WHERE b.num <= 5
SELECT * FROM t_order a 
WHERE EXISTS(SELECT orderid FROM _A b 
             WHERE a.orderid = b.orderid AND b.num <= 5); 
             
----最近5天内有订单的用户，及最后5笔订单信息
--连续几天下单用户
WITH _A AS (
    SELECT CONVERT(VARCHAR(10),createtime,111) d,userid FROM t_order 
    WHERE DATEDIFF(d, createtime, GETDATE()) <=5
    GROUP BY CONVERT(VARCHAR(10),createtime,111),userid
),
--根据连续下单用户，分组倒排订单
_B AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid 
    FROM t_order a
    WHERE datediff(d, createtime, getdate()) <=5
          AND EXISTS ( SELECT userid FROM _A b
                       WHERE a.userid = b.userid
                       GROUP BY userid
                       having count(d)>=5 )
)
--WITH _A AS (
--    SELECT userid,createtime,LEAD(createtime,4) OVER (PARTITION BY userid ORDER BY createtime) lastime
--    FROM t_order 
--    WHERE DATEDIFF(d, createtime, GETDATE()) <=5
--),
--_B AS (
--    SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid 
--    FROM t_order a
--    WHERE DATEDIFF(d, createtime, GETDATE()) <=5
--          AND EXISTS ( SELECT userid FROM _A b
--                       WHERE a.userid = b.userid
--                             AND DATEDIFF(d, b.lastime, b.createtime) <=5)
--)
SELECT * FROM t_order a 
WHERE EXISTS(SELECT orderid FROM _B b 
             WHERE a.orderid = b.orderid AND b.num <= 5);

看看有没有问题，看着调整。
速度慢的话，用注释的试一试。再者可以尝试把WITH语句换成临时表或子查询，或者换种思路解决。