CREATE TABLE [dbo].[T_UserAccess](    [Id] [int] IDENTITY(1,1) NOT NULL,    [UserId] [int] NULL,    [UserType] [int] NULL,    [OpenId] [nvarchar](50) NULL,    [UnionId] [nvarchar](50) NULL,    [CreationTime] [datetime] NULL,    [AppId] [int] NULL, CONSTRAINT [PK_T_UserAccess] PRIMARY KEY CLUSTERED (    [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

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) DecemberFROM  ( 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 userAccessGROUP BY Year