Sql Server按日、周、月、季、年统计SQL语句
当前位置：知识管理交流
→『 技术文档交流 』
--创建表
create TABLE Orders(
Id int not null identity(1,1) primary key ,
Num int,
CreationTime datetime
)
--插入数据
insert INTO Orders(Num,CreationTime)
select 98,'2021-04-21 09:24:23' union ALL
select 82,'2021-04-24 12:20:50' union ALL
select 91,'2021-04-28 11:36:29' union ALL
select 98,'2021-05-01 10:31:14' union ALL
select 84,'2021-05-02 11:42:41' union ALL
select 76,'2021-05-05 14:11:26' union ALL
select 62,'2021-05-08 15:16:34' union ALL
select 53,'2021-05-08 16:38:50' union ALL
select 32,'2021-05-10 15:16:34' union ALL
select 48,'2021-05-11 16:38:50' union ALL
select 39,'2021-05-12 15:25:11'
--按日统计
select Convert(varchar(10),CreationTime,23) 日期, count(1) 销售次数, sum(Num) 销售量
from Orders
GROUP BY Convert(varchar(10), CreationTime, 23)
--按日统计 out
日期 销售次数 销售量
2021-04-21 1 98
2021-04-24 1 82
2021-04-28 1 91
2021-05-01 1 98
2021-05-02 1 84
2021-05-05 1 76
2021-05-08 2 115
2021-05-10 1 32
2021-05-11 1 48
2021-05-12 1 39
--按周统计 (年度周次)
select datepart(week, CreationTime) 周次, count(1) 销售次数, sum(Num) 销售量
from Orders
where year(CreationTime)=year(getdate())
GROUP BY datepart(week, CreationTime)
--按周统计 (年度周次) out
周次 销售次数 销售量
17 2 180
18 2 189
19 4 275
20 3 119
--按周统计 (月份周次)
select weekName 周次,count(1) 销售次数, sum(Num) 销售量 from (
select cast(datepart(month,CreationTime) as varchar(2)) + '月第'+ cast((datepart(week,CreationTime) - datepart(week,convert(varchar(7),CreationTime,120) + '-01') + 1) as varchar(2)) + '周' weekName,Num
from Orders
where year(CreationTime)=year(getdate())
)tb
GROUP BY weekName
--可以创建函数来获取周次
create FUNCTION fn_getweek
(
@date datetime
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @result nvarchar(50)
select @result=cast(datepart(mm,@date) as varchar(2)) + '月第' + cast((datepart(wk,@date) - datepart(wk,convert(varchar(7),@date,120) + '-01') + 1) as varchar(2)) + '周';
-- Return the result of the function
RETURN @result
END
GO
select weekName 周次,count(1) 销售次数, sum(Num) 销售量 from (
select dbo.fn_getweek(CreationTime) weekName,Num
from Orders
where year(CreationTime)=year(getdate())
)tb
GROUP BY weekName
--按周统计 (月份周次) out
周次 销售次数 销售量
4月第4周 2 180
4月第5周 1 91
5月第1周 1 98
5月第2周 4 275
5月第3周 3 119
--按月统计
select convert(char(7), CreationTime, 120) 月份,count(1) 销售次数, 销售量=sum(Num)
from Orders
GROUP BY convert(char(7), CreationTime, 120)
--按月统计 out
月份 销售次数 销售量
2021-04 3 271
2021-05 8 492
--按季统计
select datepart(quarter, CreationTime) 季次, count(1) 销售次数, sum(Num) 销售量
from Orders
where year(CreationTime)=year(getdate())
GROUP BY datepart(quarter, CreationTime)
--按季统计 out
季次 销售次数 销售量
2 11 763
--按年统计
select year(CreationTime) 年次, count(1) 销售次数, sum(Num) 销售量
from Orders
GROUP BY year(CreationTime)
--按年统计 out
年次 销售次数 销售量
2021 11 763
--今天的所有数据：
select * from Orders where DATEDIFF(dd,CreationTime,GETDATE())=0
--昨天的所有数据：
select * from Orders where DATEDIFF(dd,CreationTime,GETDATE())=1
--7天内的所有数据：
select * from Orders where DATEDIFF(dd,CreationTime,GETDATE())<=7
--30天内的所有数据：
select * from Orders where DATEDIFF(dd,CreationTime,GETDATE())<=30
--本月的所有数据：
select * from Orders where DATEDIFF(mm,CreationTime,GETDATE())=0
--本年的所有数据：
select * from Orders where DATEDIFF(yy,CreationTime,GETDATE())=0
该文章在 2023/3/7 23:20:05 编辑过 |
关键字查询
相关文章
正在查询... 
|
400 186 1886
