sql server 博客分类： sql server sql server

sql如下：

--环比
SELECT  SUM(x.num) jinnian ,
        SUM(z.num) qunian ,
        x.month ,
        ( SUM(x.num) - SUM(z.num) + 0.00 ) / SUM(z.num) * 100 AS hb
FROM    ( SELECT    COUNT(*) num ,
                    YEAR(a.MD_DATE) year ,
                    MONTH(a.MD_DATE) month
          FROM      DCMD_INFO a ,
                    PUBLIC_PEOPLE_BANK b
          WHERE     a.PID = b.PID
                    AND DATEPART(yyyy, a.MD_DATE) IS NOT NULL
                    AND a.JYZT = '1'
          GROUP BY  YEAR(a.MD_DATE) ,
                    MONTH(a.MD_DATE)
        ) x
        LEFT JOIN ( SELECT  COUNT(*) num ,
                            YEAR(a.MD_DATE) year ,
                            MONTH(a.MD_DATE) month
                    FROM    DCMD_INFO a ,
                            PUBLIC_PEOPLE_BANK b
                    WHERE   a.PID = b.PID
                            AND DATEPART(yyyy, a.MD_DATE) IS NOT NULL
                            AND a.JYZT = '1'
                    GROUP BY YEAR(a.MD_DATE) ,
                            MONTH(a.MD_DATE)
                  ) z ON z.year = CASE x.month
                                    WHEN 1 THEN x.year - 1
                                    ELSE x.year
                                  END
                         AND z.month = CASE x.month
                                         WHEN 1 THEN 12
                                         ELSE x.month - 1
                                       END
WHERE   x.year = 2016
GROUP BY x.month
ORDER BY x.month;
--同比
SELECT  SUM(x.num) jinnian ,
        SUM(z.num) qunian ,
        x.month ,
        ( SUM(x.num) - SUM(z.num) + 0.00 ) / SUM(z.num) * 100 AS tb
FROM    ( SELECT    COUNT(*) num ,
                    YEAR(a.MD_DATE) year ,
                    MONTH(a.MD_DATE) month
          FROM      DCMD_INFO a ,
                    PUBLIC_PEOPLE_BANK b
          WHERE     a.PID = b.PID
                    AND DATEPART(yyyy, a.MD_DATE) IS NOT NULL
                    AND a.JYZT = '1'
          GROUP BY  YEAR(a.MD_DATE) ,
                    MONTH(a.MD_DATE)
        ) x
        LEFT JOIN ( SELECT  COUNT(*) num ,
                            YEAR(a.MD_DATE) year ,
                            MONTH(a.MD_DATE) month
                    FROM    DCMD_INFO a ,
                            PUBLIC_PEOPLE_BANK b
                    WHERE   a.PID = b.PID
                            AND DATEPART(yyyy, a.MD_DATE) IS NOT NULL
                            AND a.JYZT = '1'
                    GROUP BY YEAR(a.MD_DATE) ,
                            MONTH(a.MD_DATE)
                  ) z ON z.year = x.year-1 AND z.month = x.month
WHERE   x.year = 2016
GROUP BY x.month
ORDER BY x.month;