sql语句整理1--统计当月签到次数最多

SELECT b.id "userid" , COUNT(*) "count" FROM

       (SELECT r,

       id,

       (CASE

         WHEN ((SELECT DA

                  from (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC) te1

                 WHERE te1.r = a.r+1) -

              nvl((SELECT DA

                     from (SELECT rownum r, test11.*

                             FROM test11

                            ORDER BY da DESC) te2

                    WHERE te2.r = a.r ),

                   sysdate) > 1) THEN

          0

         ELSE

          1

       END) cu

  FROM (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC)a ) b

  WHERE b.cu = 1 AND b.r   > ( SELECT MAX(c.r) FROM (SELECT r,

       id,

       (CASE

         WHEN ((SELECT DA

                  from (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC) te1

                 WHERE te1.r = a.r+1) -

              nvl((SELECT DA

                     from (SELECT rownum r, test11.*

                             FROM test11

                            ORDER BY da DESC) te2

                    WHERE te2.r = a.r ),

                   sysdate) > 1) THEN

          0

         ELSE

          1

       END) cu

  FROM (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC)a ) c  where c.id = b.id GROUP BY c.cu HAVING c.cu = 0 )   GROUP BY b.id