sql 根据年份、月份查询数据

sql 根据年份、月份查询数据

SELECT ISNULL(NEWID(), ‘d1e57ca7-6eee-495a-be13-73d5e7d51f36‘) AS Id,
       userAccess.Year,
       Sum(January) January,
       Sum(February) February,
       Sum(March) March,
       Sum(April) April,
       Sum(May) May,
       Sum(June) June,
       Sum(July) July,
       Sum(August) August,
       Sum(September) September,
       Sum(October) October,
       Sum(November) November,
       Sum(December) December
FROM
  ( SELECT years AS ‘Year‘,
           CASE
               WHEN months=1 THEN counts
               ELSE 0
           END ‘January‘,
           CASE
                WHEN months=2 THEN counts
                ELSE 0
            END ‘February‘,
                CASE
                WHEN months=3 THEN counts
                ELSE 0
                END ‘March‘,
            CASE
                WHEN months=4 THEN counts
                ELSE 0
                END ‘April‘,
                CASE
                WHEN months=5 THEN counts
                ELSE 0
                END ‘May‘,
            CASE
                WHEN months=6 THEN counts
                ELSE 0
                END ‘June‘,
            CASE
                WHEN months=7 THEN counts
                ELSE 0
                END ‘July‘,
            CASE
                WHEN months=8 THEN counts
                ELSE 0
                END ‘August‘,
            CASE
                WHEN months=9 THEN counts
                ELSE 0
                END ‘September‘,
            CASE
                WHEN months=10 THEN counts
                ELSE 0
                END ‘October‘,
            CASE
                WHEN months=11 THEN counts
                ELSE 0
                END ‘November‘,
            CASE
                WHEN months=12 THEN counts
                ELSE 0
                END ‘December‘
   FROM
     (SELECT datepart(yy,CreationTime) AS years,
             datepart(mm,CreationTime) months,
                                       count(1)AS counts
      FROM [T_UserAccess]
      GROUP BY year(CreationTime),
               month(CreationTime))AS ua ) AS userAccess
GROUP BY Year

sql 根据年份、月份查询数据

标签：case   font   app   div   clu   creation   pid   ril   identity