比较账户的资金流水

需求：最近资金流水是否有跳跃，间断。
思路：分组获取用户最新的几条资金流水，再比较是否有资金间断。
MySQL

1. 分组片段

select t.pre_money money1, t.uid
from (
         select t1.*,
                (select count(*) + 1
                 from (
                          select id,
                                 balance_type,
                                 uid,
                                 money,
                                 balance,
                                 if((type >= 21 && type <= 36), (money + balance), (balance - money)) as pre_money,
                                 type,
                                 created_at
                          from prefix_member_balance_record
                          where uid in (
                              select uid
                              from prefix_member_balance_record
                              where balance_type = 1
                                and created_at > 1615482600
                                and created_at < 1615541400
                              group by uid
                          )
                            and balance_type = 1
                            and created_at > 1615165200
                            and created_at < 1615541400
                      ) t2
                 where t2.uid = t1.uid
                   and t2.id > t1.id) top
         from (
                  select id,
                         balance_type,
                         uid,
                         money,
                         balance,
                         if((type >= 21 && type <= 36), (money + balance), (balance - money)) as pre_money,
                         type,
                         created_at
                  from prefix_member_balance_record
                  where uid in (
                      select uid
                      from prefix_member_balance_record
                      where balance_type = 1
                        and created_at > 1615482600
                        and created_at < 1615541400
                      group by uid
                  )
                    and balance_type = 1
                    and created_at > 1615165200
                    and created_at < 1615541400
              ) t1
     ) t
where top <= 3
order by t.uid, top;

2. 比较

select *
from (
         select *
         from (select t.pre_money money1, t.uid
               from (
                        select t1.*,
                               (select count(*) + 1
                                from (
                                         select id,
                                                balance_type,
                                                uid,
                                                money,
                                                balance,
                                                case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                                type,
                                                created_at
                                         from prefix_member_balance_record
                                         where uid in (
                                             select uid
                                             from prefix_member_balance_record
                                             where balance_type = 3
                                               and created_at > 1615482600
                                               and created_at < 1615541400
                                             group by uid
                                         )
                                           and balance_type = 3
                                           and created_at > 1615165200
                                           and created_at < 1615541400
                                     ) t2
                                where t2.uid = t1.uid
                                  and t2.created_at > t1.created_at) top
                        from (
                                 select id,
                                        balance_type,
                                        uid,
                                        money,
                                        balance,
                                        case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                        type,
                                        created_at
                                 from prefix_member_balance_record
                                 where uid in (
                                     select uid
                                     from prefix_member_balance_record
                                     where balance_type = 3
                                       and created_at > 1615482600
                                       and created_at < 1615541400
                                     group by uid
                                 )
                                   and balance_type = 3
                                   and created_at > 1615165200
                                   and created_at < 1615541400
                             ) t1
                    ) t
               where top = 1
               order by t.uid, top
              ) f1
                  left join (
             select t.balance money2, t.uid, t.pre_money money3
             from (
                      select t1.*,
                             (select count(*) + 1
                              from (
                                       select id,
                                              balance_type,
                                              uid,
                                              money,
                                              balance,
                                              case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                              type,
                                              created_at
                                       from prefix_member_balance_record
                                       where uid in (
                                           select uid
                                           from prefix_member_balance_record
                                           where balance_type = 3
                                             and created_at > 1615482600
                                             and created_at < 1615541400
                                           group by uid
                                       )
                                         and balance_type = 3
                                         and created_at > 1615165200
                                         and created_at < 1615541400
                                   ) t2
                              where t2.uid = t1.uid
                                and t2.created_at > t1.created_at) top
                      from (
                               select id,
                                      balance_type,
                                      uid,
                                      money,
                                      balance,
                                      case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                      type,
                                      created_at
                               from prefix_member_balance_record
                               where uid in (
                                   select uid
                                   from prefix_member_balance_record
                                   where balance_type = 3
                                     and created_at > 1615482600
                                     and created_at < 1615541400
                                   group by uid
                               )
                                 and balance_type = 3
                                 and created_at > 1615165200
                                 and created_at < 1615541400
                           ) t1
                  ) t
             where top = 2
             order by t.uid, top
         ) f2 on f1.uid = f2.uid
                  left join (
             select t.balance money4, t.uid
             from (
                      select t1.*,
                             (select count(*) + 1
                              from (
                                       select id,
                                              balance_type,
                                              uid,
                                              money,
                                              balance,
                                              case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                              type,
                                              created_at
                                       from prefix_member_balance_record
                                       where uid in (
                                           select uid
                                           from prefix_member_balance_record
                                           where balance_type = 3
                                             and created_at > 1615482600
                                             and created_at < 1615541400
                                           group by uid
                                       )
                                         and balance_type = 3
                                         and created_at > 1615165200
                                         and created_at < 1615541400
                                   ) t2
                              where t2.uid = t1.uid
                                and t2.created_at > t1.created_at) top
                      from (
                               select id,
                                      balance_type,
                                      uid,
                                      money,
                                      balance,
                                      # if( (type >=21 && type <=36),(money + balance) , (balance - money) ) as pre_money,
                                      case type when 40 then (money + balance) else (balance - money) end as pre_money,
                                      type,
                                      created_at
                               from prefix_member_balance_record
                               where uid in (
                                   select uid
                                   from prefix_member_balance_record
                                   where balance_type = 3
                                     and created_at > 1615482600
                                     and created_at < 1615541400
                                   group by uid
                               )
                                 and balance_type = 3
                                 and created_at > 1615165200
                                 and created_at < 1615541400
                           ) t1
                  ) t
             where top = 3
             order by t.uid, top
         ) f3 on f2.uid = f3.uid
     ) fff
where money1 != money2
   or money3 != money4;

3. 结果（资金间断数据）
   
4. 查看原流水检测结果（增加是减，减少即是加）
   
   最后感谢此博主提供的原始知识的帮助：
   第一位
   SQL分组查询后取每组的前N条记录
   第二位博主
   mysql分组取最大(最小、最新、前N条)条记录

### 查找指定商品组arr，每个商品的最近的7条销量
SELECT `object_id`, `vol`, `origin_at`
FROM `goods_extend` `a`
WHERE ((`object_id` IN
        (642713683122, 666934159590, 652604706119, 647048583819, 610872171140, 662546489567, 658150433258, 653220978258,
         598927886912, 657521611566)) AND (`source` = '2') AND (`is_del` = 0))
  AND ((SELECT COUNT(1)
        FROM `goods_extend` `b`
        WHERE a.source = b.source
          and a.object_id = b.object_id
          and b.id > a.id) < 7)
ORDER BY `object_id`, `id`;

总结：
比如id数据为[13, 12, 11, 32, 55]

a.id取值有[13, 12, 11, 32, 55]
b.id取值也是[13, 12, 11, 32, 55]
b.id > a.id的个数，针对每个a.id有，[2, 3, 4, 1, 0], 在b子查询里大13的个数有2个，大于12的个数有3个，以此类推，类似冒泡排序

然后取个数小于2的所有id,对应的a里的id，就是最大的两个数,32和55？？