Sql Server 查询指定范围（一周，一月，本周，本月等）内的数据，及产生一段时间内所有日期

最新推荐文章于 2024-08-06 17:03:59 发布

原创最新推荐文章于 2024-08-06 17:03:59 发布 · 8.2k 阅读

26 ·

CC 4.0 BY-SA版权

MSSQL 同时被 2 个专栏收录

65 篇文章

订阅专栏

SQL相关

53 篇文章

订阅专栏

本文介绍如何使用SQL Server查询过去一周、一个月等特定时间范围内的数据，并展示如何生成某一时间段内的所有日期记录。

1.查询今日的所有数据

select * from 表名 where datediff(day,字段名,getdate())=0

2.查询昨日的所有数据

select * from 表名 where datediff(day,字段名,getdate()-1)=0

3.查询当天日期在一周年的数据

selcet * from 表名 where datediff(week,字段名,getdate()-1)=0

4.查询前30天的数据

select * from 表名 where datediff(d,字段名,getdate())<=30

5.查询上一个月的数据

select * from 表名 where datediff(m,字段名,getdate())<=1

6.查询当天的数据

select * from 表名 where datediff(dd,字段名,getdate())=0

7.查询24小时内的数据

select * from 表名 where datediff(hh,字段名,getdate())

8.查询本周的数据

select * from 表名 where datediff(week,字段名,getdate())=0

9.查询本月的数据

select * from 表名 where datediff(month,字段名,getdate())=0

10.查询本季的数据

select * from 表名 where datediff(qq,字段名,getdate())=0

CREATE VIEW view_BaseAllYear
AS
--生成一段时间内的日期(一年内)
SELECT CONVERT(NVARCHAR(10), DATEADD(DAY,number,DATEADD(YEAR,-1,GETDATE())),120) AS GroupDate  
FROM master..spt_values  
WHERE type = 'p' AND number <= DATEDIFF(DAY,DATEADD(YEAR,-1,GETDATE()),GETDATE())

CREATE VIEW view_BaseThisMonth
AS
--生成一段时间内的日期(一月内)
SELECT CONVERT(NVARCHAR(10), DateAdd(day,number,DATEADD(MONTH,-1,GETDATE())),120) AS GroupDate  
FROM master..spt_values  
WHERE type = 'p' AND number <= DATEDIFF(DAY,DATEADD(MONTH,-1,GETDATE()),GETDATE())

ALTER PROCEDURE pr_GetBetweenDays
@StartDate NVARCHAR(50) ,--开始时间  
@EndDate NVARCHAR(50) --结束时间  
AS 
BEGIN
	--产生一段时间内的所有日期
	Declare @sql nvarchar(4000);   
	SET @sql = 
	 '
			SELECT CONVERT(NVARCHAR(10), DateAdd(day,number,'''+@StartDate+'''),120) AS GroupDay  
			FROM master..spt_values  
			WHERE type = ''p'' AND number <= DateDiff(day,'''+@StartDate+''','''+@EndDate+''') 
	'
	PRINT @sql  
    EXEC(@sql)  
END


--EXECUTE pr_GetBetweenDays @StartDate = '2014-09-29', @EndDate = '2015-09-29'

--按周统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(week,AddDate) 周,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(week,AddDate)
ORDER BY CONVERT(INT,DATENAME(week,AddDate));

----------------------------------------------------------------
--按月统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(month,AddDate) 月,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(month,AddDate)
ORDER BY CONVERT(INT,DATENAME(month,AddDate));

----------------------------------------------------------------
--按季度统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
DATENAME(quarter,AddDate) 季度,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate),DATENAME(quarter,AddDate)
ORDER BY CONVERT(INT,DATENAME(quarter,AddDate));

----------------------------------------------------------------
--按年统计
SELECT TOP 10
DATENAME(year,AddDate) 年,
COUNT(1) 单量,
SUM(total) 总金额,
AVG(total) 客单价
FROM orderinfo t(NOLOCK)
WHERE AddDate BETWEEN '2012-1-1' AND '2014-1-1'
GROUP BY DATENAME(year,AddDate)
ORDER BY CONVERT(INT,DATENAME(year,AddDate));