62,017
社区成员
发帖
与我相关
我的任务
分享
select (子查询1) as 上月值, (子查询2) as 本月值
就可以了。USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[n] INT
,[d] DATETIME
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'3',N'2018-6-20')
INSERT INTO dbo.[t] VALUES(N'1',N'2018-7-1')
INSERT INTO dbo.[t] VALUES(N'5',N'2018-7-31')
INSERT INTO dbo.[t] VALUES(N'4',N'2018-8-7')
INSERT INTO dbo.[t] VALUES(N'7',N'2018-8-17')
INSERT INTO dbo.[t] VALUES(N'8',N'2018-9-18')
---- 以上为测试表与测试数据 ----------
--创建索引提高效率,单独执行
CREATE INDEX ix_t_d ON t(d)
DECLARE @m VARCHAR(7)
SET @m ='201807' --传入月份
--注意左边的 d 字段不要加任何包装,这样才能高效
SELECT
SUM(CASE WHEN d >= @m+'01' AND d< DATEADD(MONTH,1,@m+'01') THEN n ELSE 0 END) AS [当前月数量]
,SUM(CASE WHEN d >= DATEADD(MONTH,1,@m+'01') AND d< DATEADD(MONTH,2,@m+'01') THEN n ELSE 0 END) AS [下个月数量]
FROM t
/*
当前月数量 下个月数量
----------- -----------
6 11
*/
SELECT YEAR(adddate)年,MONTH(adddate) 月, SUM(sale) 数量
FROM test5
GROUP BY YEAR(adddate),MONTH(adddate)