SQL创建视图语句求助

[相汝以沫]

求助一个创建视图的语句
源数据有两个字段，时间和分类（分类里面有重复的内容）
想创建一个视图，首先对分类按照月进行分组，统计出这个月总数前10的项目，列出本月对应的数量，以及上个月对应数量

[雨夹雪]

--建表
CREATE TABLE T
(
	时间 DATETIME,
	分类 VARCHAR(10)
)  
--测试数据
INSERT INTO T VALUES('2020-11-11','A')
INSERT INTO T VALUES('2020-11-11','B')
INSERT INTO T VALUES('2020-11-11','C')
INSERT INTO T VALUES('2020-11-12','A')
INSERT INTO T VALUES('2020-11-13','D')

INSERT INTO T VALUES('2020-10-1','A')
INSERT INTO T VALUES('2020-10-2','B')
INSERT INTO T VALUES('2020-9-3','C')
INSERT INTO T VALUES('2020-9-4','A')
INSERT INTO T VALUES('2020-9-8','D')

--1.如果只需要最近两个月的数据,可以这样
go
CREATE VIEW v_t1
AS
SELECT 分类,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),GETDATE(),121)+'-01' THEN 1 ELSE 0 end ) AS 当月,
SUM( CASE WHEN 时间< CONVERT(VARCHAR(7),GETDATE(),121)+'-01' THEN 1 ELSE 0 end ) AS 上月
FROM dbo.T
WHERE 时间>=CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01'  AND 时间< CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01'
GROUP BY 分类 
GO
SELECT * FROM v_t1
GO

--2.如果需要任意月份的数据,可以这样
CREATE VIEW v_t2
AS
WITH Ct 
AS
(
	SELECT 分类,CONVERT(varchar(7),时间,121) AS 月份,COUNT(1) AS 当月  
	FROM dbo.T GROUP BY 分类,CONVERT(varchar(7),时间,121) 
) 
SELECT 
a.分类,A.月份,A.当月,B.当月 AS 上月
FROM ct a 
LEFT JOIN ct b ON a.分类 = b.分类 AND a.月份+'-01' =DATEADD(MONTH,1, b.月份+'-01') 

GO
SELECT 分类,当月,上月 FROM v_t2 WHERE 月份 ='2020-11'
 
 

[相汝以沫]

谢谢大神，基本实现了我的述求 但是不知道为什么，有的时候会出现当月数量是0的情况，不知道为什么

[雨夹雪]

你用的第一种情况 这个是查询最近两个月的数据,然后分类统计的,你的数据是上月有当月没有,所以是这样 要是你不需要显示当月是0的数据,加个where条件 过滤一下就可以了

[相汝以沫]

引用 3 楼 雨夹雪的回复:

你用的第一种情况 这个是查询最近两个月的数据,然后分类统计的,你的数据是上月有当月没有,所以是这样 要是你不需要显示当月是0的数据,加个where条件 过滤一下就可以了

嗯嗯，我加了，谢谢。

[相汝以沫]

引用 3 楼 雨夹雪 的回复:

你用的第一种情况 这个是查询最近两个月的数据,然后分类统计的,你的数据是上月有当月没有,所以是这样 要是你不需要显示当月是0的数据,加个where条件 过滤一下就可以了

CONVERT(VARCHAR(7),GETDATE(),121)+'-01' 这个+'-01'是啥意思啊

[雨夹雪]

go
CREATE VIEW v_t1
AS
SELECT 分类,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),GETDATE(),121)+'-01' AND 时间< CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01' ) THEN 1 ELSE 0 END  AS 当月,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01' AND 时间< CONVERT(VARCHAR(7),GETDATE(),121)+'-01' THEN 1 ELSE 0 end ) AS 上月,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01' AND 时间<CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01' THEN 1 ELSE 0 end ) AS 上上月
FROM dbo.T
WHERE 时间>=CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01'  AND 时间< CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01'
GROUP BY 分类 
GO
SELECT * FROM v_t1
GO

/*
CONVERT(VARCHAR(7),GETDATE(),121)+'-01' 当月的1号
CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01' )  下个月的1号
CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01'   上个月的1号
CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01'   上上个月的1号

大于等于当月的1号 到 小于下个月的1号 是当月
大于等于上个月的1号 到小于当月的1号  是上月
大于等于上上个月的1号 到 小于上个月的1号 是上上月

*/

[相汝以沫]

引用 7 楼 雨夹雪的回复:

go
CREATE VIEW v_t1
AS
SELECT 分类,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),GETDATE(),121)+'-01' AND 时间< CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01' ) THEN 1 ELSE 0 END  AS 当月,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01' AND 时间< CONVERT(VARCHAR(7),GETDATE(),121)+'-01' THEN 1 ELSE 0 end ) AS 上月,
SUM( CASE WHEN 时间>=CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01' AND 时间<CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01' THEN 1 ELSE 0 end ) AS 上上月
FROM dbo.T
WHERE 时间>=CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01'  AND 时间< CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01'
GROUP BY 分类 
GO
SELECT * FROM v_t1
GO

/*
CONVERT(VARCHAR(7),GETDATE(),121)+'-01' 当月的1号
CONVERT(VARCHAR(7),DATEADD(m,1,GETDATE()),121)+'-01' )  下个月的1号
CONVERT(VARCHAR(7),DATEADD(m,-1,GETDATE()),121)+'-01'   上个月的1号
CONVERT(VARCHAR(7),DATEADD(m,-2,GETDATE()),121)+'-01'   上上个月的1号

大于等于当月的1号 到 小于下个月的1号 是当月
大于等于上个月的1号 到小于当月的1号  是上月
大于等于上上个月的1号 到 小于上个月的1号 是上上月

*/

谢谢，我自己也弄出来了，第二种如果加上上月是再连接一个left join是吧，我用这种方法实现了。不知道是不是最好的方式？