SQLSERVER 统计今天、本周、本月数据_sqlserver 按照日期分组统计本周数据-CSDN博客

本文详细介绍如何使用SQL Server和MySQL进行日期相关的数据统计，包括按日、周、月、季统计数据的方法，以及如何统计特定时间段内的记录。通过多个示例，读者可以学习到DATEPART、DATEDIFF等函数的应用，以及如何聚合数据。

转载出处：https://blog.csdn.net/qq285679784/article/details/80426762

eg:
selectasset_info_src_cd,count(*) from asset wheredatediff(week,asset_setup_dt,getdate())=0
group by asset_info_src_cd
eg:
select asset_info_src_cd,count (asset_info_src_cd) from asset where datepart(week,asset_setup_dt) =datepart(week,getdate())
group by asset_info_src_cd
eg:
select asset_info_src_cd,count(*) from asset where asset_setup_dt >'20150706' and asset_setup_dt <=getdate()
group by asset_info_src_cd
本月统计(MySQL)
select * from booking where month(booking_time) = month(curdate()) and year(booking_time) = year(curdate())
本周统计(MySQL)
select * from spf_booking where month(booking_time) = month(curdate()) and week(booking_time) = week(curdate())
[SQLServer]
表名为：tableName
时间字段名为：theDate
查询本月的记录
select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE())
and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本周的记录
select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE())
and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本季的记录
select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE())
and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
其中:GETDATE()是获得系统时间的函数。
如:
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006'
group by day([date])
--按周quarter
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006'
group by datename(week,[date])
--按月
select sum(consume),month([date]) from consume_record where year([date]) = '2006'
group by month([date])
--按季
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006'
group by datename(quarter,[date])
--指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by
select [date],sum(consume) from consume_record where [date] between '2006-06-01' and '2006-07-10'
group by [date]
统计博客聚合用户点击次数
SELECT author, SUM(hits) AS hits
FROM infos
GROUP BY author
ORDER BY hits DESC
统计昨天的记录
SELECT *
FROM infos
WHERE (DATEDIFF(d, pubdate, GETDATE()) = 1)
统计本周的记录
SELECT *
FROM infos
WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART(week,
pubdate - 1) = DATEPART(week, GETDATE()))
统计本月的记录:
SELECT *
FROM infos
WHERE (DATEPART(yy, pubdate) = DATEPART(yy, GETDATE())) AND (DATEPART([month],
pubdate - 1) = DATEPART([month], GETDATE()))
eg:
select asset_info_src_cd,count (asset_info_src_cd) from asset where datepart(week,asset_setup_dt) =datepart(week,getdate())
group by asset_info_src_cd
eg:
select asset_info_src_cd,count(*) from asset where asset_setup_dt >'20150706' and asset_setup_dt <=getdate()
group by asset_info_src_cd

SQL Server DATEPART() 函数

SQL Server Date 函数

定义和用法

DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法

DATEPART(datepart,date)

date 参数是合法的日期表达式。datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

实例

假设我们有下面这个 "Orders" 表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

我们使用如下 SELECT 语句：

SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

结果：

OrderYear	OrderMonth	OrderDay
2008	12	29

SQL Server Date 函数