有如下2张表:
CREATE TABLE [dbo].[T_User](
[id] [int] IDENTITY(1,1) NOT NULL,
[uname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_T_User] 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]GO
ALTER TABLE [dbo].[T_User] WITH CHECK ADD CONSTRAINT [FK_T_User_T_User] FOREIGN KEY([id])
REFERENCES [dbo].[T_User] ([id])
GO
ALTER TABLE [dbo].[T_User] CHECK CONSTRAINT [FK_T_User_T_User];CREATE TABLE [dbo].[T_Vistor](
[id] [int] IDENTITY(1,1) NOT NULL,
[vtime] [datetime] NOT NULL,
[userid] [int] NOT NULL,
CONSTRAINT [PK_T_Vistor] 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]GO
ALTER TABLE [dbo].[T_Vistor] WITH CHECK ADD CONSTRAINT [FK_T_Vistor_T_User] FOREIGN KEY([userid])
REFERENCES [dbo].[T_User] ([id])
GO
ALTER TABLE [dbo].[T_Vistor] CHECK CONSTRAINT [FK_T_Vistor_T_User];
要怎么样处理才能查询得到如下内容:
用户名 本周访问次数 本月访问次数 历史访问次数
CREATE TABLE [dbo].[T_User](
[id] [int] IDENTITY(1,1) NOT NULL,
[uname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_T_User] 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]GO
ALTER TABLE [dbo].[T_User] WITH CHECK ADD CONSTRAINT [FK_T_User_T_User] FOREIGN KEY([id])
REFERENCES [dbo].[T_User] ([id])
GO
ALTER TABLE [dbo].[T_User] CHECK CONSTRAINT [FK_T_User_T_User];CREATE TABLE [dbo].[T_Vistor](
[id] [int] IDENTITY(1,1) NOT NULL,
[vtime] [datetime] NOT NULL,
[userid] [int] NOT NULL,
CONSTRAINT [PK_T_Vistor] 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]GO
ALTER TABLE [dbo].[T_Vistor] WITH CHECK ADD CONSTRAINT [FK_T_Vistor_T_User] FOREIGN KEY([userid])
REFERENCES [dbo].[T_User] ([id])
GO
ALTER TABLE [dbo].[T_Vistor] CHECK CONSTRAINT [FK_T_Vistor_T_User];
要怎么样处理才能查询得到如下内容:
用户名 本周访问次数 本月访问次数 历史访问次数
--没看明白,用户和角色放同一张表?
--用户名 本周访问次数 本月访问次数 历史访问次数
--就三个尺度,先统计了再union,再行转列如何
[周次数]=count(case when datediff(wk,a.vtime,getdate())=0 then 1 else null end)
[月次数]=count(case when datediff(mm,a.vtime,getdate())=0 then 1 else null end)
[总次数]=count(*)
from [T_Vistor] a left join [T_User] b
on a.[userid]=b.[id]
group by b.uname
[周次数]=count(case when datediff(wk,a.vtime,getdate())=0 then 1 else null end),
[月次数]=count(case when datediff(mm,a.vtime,getdate())=0 then 1 else null end),
[总次数]=count(*)
from [T_Vistor] a left join [T_User] b
on a.[userid]=b.[id]
group by b.uname
[id] [int] IDENTITY(1,1) NOT NULL,
[uname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_T_User] 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]GO
CREATE TABLE [dbo].[T_Vistor](
[id] [int] IDENTITY(1,1) NOT NULL,
[vtime] [datetime] NOT NULL,
[userid] [int] NOT NULL,
CONSTRAINT [PK_T_Vistor] 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];WITH CTE AS
(
SELECT A.uname
,'周' AS TYPE
,COUNT(B.vtime) AS NUM
FROM [T_User] A JOIN [T_Vistor] B ON A.id=B.userid
WHERE B.vtime BETWEEN GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@DATEFIRST-1)+1 AND GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@DATEFIRST-1)+7
--GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@DATEFIRST-1)+1 星期1
--GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@DATEFIRST-1)+7 星期日
GROUP BY A.[uname],A.id
UNION ALL
SELECT A.uname
,'月' AS TYPE
,COUNT(B.vtime) AS NUM
FROM [T_User] A JOIN [T_Vistor] B ON A.id=B.userid
WHERE DATEDIFF(MM,B.vtime,GETDATE())=0
GROUP BY A.[uname],A.id
UNION ALL
SELECT A.uname
,'历史' AS TYPE
,COUNT(B.vtime) AS NUM
FROM [T_User] A JOIN [T_Vistor] B ON A.id=B.userid
GROUP BY A.[uname],A.id
)
SELECT * FROM CTE
PIVOT(SUM(NUM) FOR TYPE IN([周],[月],[历史])) AS P
[周次数]=count(case when datediff(wk,a.vtime,getdate())=0 then 1 else null end),
[月次数]=count(case when datediff(mm,a.vtime,getdate())=0 then 1 else null end),
[总次数]=count(*)
from [T_Vistor] a left join [T_User] b
on a.[userid]=b.[id]
group by b.uname'=' 附近有语法错误。