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) 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