--创建表
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