;with t as( select DATEADD(dd,number,'2012-10-10') dt, datepart(WK,DATEADD(dd,number,'2012-10-10')) as wk, number from master..spt_values where number between 0 and DATEDIFF(dd,'2012-10-10','2012-11-30') and type='p' ) select YEAR(dt) as Years, wk, count(1) as CountDays, sum(number) as counts from t group by YEAR(dt), wk /* Years(年) wk(周) CountDays(这一周多少天) counts(number的求和) ----------- ----------- ----------- ----------- 2012 41 4 6 2012 42 7 49 2012 43 7 98 2012 44 7 147 2012 45 7 196 2012 46 7 245 2012 47 7 294 2012 48 6 291(8 行受影响) */
類似於這樣? select year(日期) as [Year],datepart(week,日期) as [Week],count(1) as [Count] from tb Group by year(日期),datepart(week,日期)
USE test GO ---->生成表tb --if object_id('tb') is not null -- drop table tb --Go ---- test data --Create table tb([日期] datetime,[OrderNr] nvarchar(3),[CustomerName] nvarchar(1)) --Insert into tb --Select '2012-01-01',N'#1',N'A' --Union all Select '2012-01-02',N'#2',N'A' --Union all Select '2012-01-03',N'#3',N'A' --Union all Select '2012-01-04',N'#4',N'A' --Union all Select '2012-02-01',N'#5',N'A' --Union all Select '2012-02-02',N'#6',N'B' --Union all Select '2012-02-03',N'#7',N'B' --Union all Select '2012-05-14',N'#8',N'B' --Union all Select '2012-05-16',N'#9',N'C' --Union all Select '2012-05-18',N'#10',N'B' --Union all Select '2012-05-19',N'#11',N'C' --Union all Select '2012-09-05',N'#12',N'A' --Union all Select '2012-09-06',N'#13',N'D' --Union all Select '2012-09-07',N'#14',N'D' SELECT COUNT(a.日期) AS [Count] ,b.[Week] ,b.[Year] FROM tb AS a RIGHT JOIN ( SELECT * FROM ( SELECT YEAR(日期) AS [Year] FROM tb GROUP BY YEAR(日期) ) t,( SELECT TOP 52 ROW_NUMBER()OVER(ORDER BY getdate()) AS [Week] FROM sys.syscolumns ) t2 ) AS b ON YEAR(a.日期)=b.Year AND DATEPART(week,a.日期)=b.Week GROUP BY b.[Week],b.[Year]
添加篩選:週期範圍USE test GO ---->生成表tb --if object_id('tb') is not null -- drop table tb --Go ---- test data --Create table tb([日期] datetime,[OrderNr] nvarchar(3),[CustomerName] nvarchar(1)) --Insert into tb --Select '2012-01-01',N'#1',N'A' --Union all Select '2012-01-02',N'#2',N'A' --Union all Select '2012-01-03',N'#3',N'A' --Union all Select '2012-01-04',N'#4',N'A' --Union all Select '2012-02-01',N'#5',N'A' --Union all Select '2012-02-02',N'#6',N'B' --Union all Select '2012-02-03',N'#7',N'B' --Union all Select '2012-05-14',N'#8',N'B' --Union all Select '2012-05-16',N'#9',N'C' --Union all Select '2012-05-18',N'#10',N'B' --Union all Select '2012-05-19',N'#11',N'C' --Union all Select '2012-09-05',N'#12',N'A' --Union all Select '2012-09-06',N'#13',N'D' --Union all Select '2012-09-07',N'#14',N'D' --Union all Select '2013-01-06',N'#14',N'D' --Union all Select '2013-01-07',N'#14',N'D' ;WITH Result AS ( SELECT YEAR(日期) AS [Year],DATEPART(Week,日期) AS [Week],* FROM tb ) SELECT COUNT(a.日期) AS [Count] ,b.[Week] ,b.[Year] FROM Result AS a RIGHT JOIN ( SELECT t.Year,t2.Week FROM ( SELECT DISTINCT Year FROM Result ) t,( SELECT TOP 52 ROW_NUMBER()OVER(ORDER BY getdate()) AS [Week] FROM sys.syscolumns ) t2 WHERE t2.Week>=(SELECT MIN(Week) FROM Result AS x WHERE x.Year=t.Year) AND t2.Week<=(SELECT MAX(Week) FROM Result AS x WHERE x.Year=t.Year) ) AS b ON a.Year=b.Year AND a.Week=b.Week GROUP BY b.[Week],b.[Year] ORDER BY b.[Year],b.[Week] /* Count Week Year ------ ----- ----- 4 1 2012 0 2 2012 0 3 2012 0 4 2012 3 5 2012 0 6 2012 0 7 2012 0 8 2012 0 9 2012 0 10 2012 0 11 2012 0 12 2012 0 13 2012 0 14 2012 0 15 2012 0 16 2012 0 17 2012 0 18 2012 0 19 2012 4 20 2012 0 21 2012 0 22 2012 0 23 2012 0 24 2012 0 25 2012 0 26 2012 0 27 2012 0 28 2012 0 29 2012 0 30 2012 0 31 2012 0 32 2012 0 33 2012 0 34 2012 0 35 2012 3 36 2012 2 2 2013 */
select
DATEADD(dd,number,'2012-10-10') dt,
datepart(WK,DATEADD(dd,number,'2012-10-10')) as wk
from
master..spt_values
where
number between 0 and DATEDIFF(dd,'2012-10-10','2012-11-30')
and type='p'
/*
dt wk
----------------------- -----------
2012-10-10 00:00:00.000 41
2012-10-11 00:00:00.000 41
2012-10-12 00:00:00.000 41
2012-10-13 00:00:00.000 41
2012-10-14 00:00:00.000 42
2012-10-15 00:00:00.000 42
2012-10-16 00:00:00.000 42
2012-10-17 00:00:00.000 42
2012-10-18 00:00:00.000 42
2012-10-19 00:00:00.000 42
2012-10-20 00:00:00.000 42
2012-10-21 00:00:00.000 43
2012-10-22 00:00:00.000 43
2012-10-23 00:00:00.000 43
2012-10-24 00:00:00.000 43
2012-10-25 00:00:00.000 43
2012-10-26 00:00:00.000 43
2012-10-27 00:00:00.000 43
2012-10-28 00:00:00.000 44
2012-10-29 00:00:00.000 44
2012-10-30 00:00:00.000 44
2012-10-31 00:00:00.000 44
2012-11-01 00:00:00.000 44
2012-11-02 00:00:00.000 44
2012-11-03 00:00:00.000 44
2012-11-04 00:00:00.000 45
2012-11-05 00:00:00.000 45
2012-11-06 00:00:00.000 45
2012-11-07 00:00:00.000 45
2012-11-08 00:00:00.000 45
2012-11-09 00:00:00.000 45
2012-11-10 00:00:00.000 45
2012-11-11 00:00:00.000 46
2012-11-12 00:00:00.000 46
2012-11-13 00:00:00.000 46
2012-11-14 00:00:00.000 46
2012-11-15 00:00:00.000 46
2012-11-16 00:00:00.000 46
2012-11-17 00:00:00.000 46
2012-11-18 00:00:00.000 47
2012-11-19 00:00:00.000 47
2012-11-20 00:00:00.000 47
2012-11-21 00:00:00.000 47
2012-11-22 00:00:00.000 47
2012-11-23 00:00:00.000 47
2012-11-24 00:00:00.000 47
2012-11-25 00:00:00.000 48
2012-11-26 00:00:00.000 48
2012-11-27 00:00:00.000 48
2012-11-28 00:00:00.000 48
2012-11-29 00:00:00.000 48
2012-11-30 00:00:00.000 48(52 行受影响)
*/
count week year
2 42 2012
4 43 2012
7 44 2012
28 45 2012
98 46 2012
148 47 2012
65 48 2012就是类似于这种的。count就是这周的数据量,week是第几周
as(
select
DATEADD(dd,number,'2012-10-10') dt,
datepart(WK,DATEADD(dd,number,'2012-10-10')) as wk,
number
from
master..spt_values
where
number between 0 and DATEDIFF(dd,'2012-10-10','2012-11-30')
and type='p'
)
select
YEAR(dt) as Years,
wk,
count(1) as CountDays,
sum(number) as counts
from
t
group by
YEAR(dt),
wk
/*
Years(年) wk(周) CountDays(这一周多少天) counts(number的求和)
----------- ----------- ----------- -----------
2012 41 4 6
2012 42 7 49
2012 43 7 98
2012 44 7 147
2012 45 7 196
2012 46 7 245
2012 47 7 294
2012 48 6 291(8 行受影响)
*/
select year(日期) as [Year],datepart(week,日期) as [Week],count(1) as [Count] from tb Group by year(日期),datepart(week,日期)
GO
---->生成表tb
--if object_id('tb') is not null
-- drop table tb
--Go
---- test data
--Create table tb([日期] datetime,[OrderNr] nvarchar(3),[CustomerName] nvarchar(1))
--Insert into tb
--Select '2012-01-01',N'#1',N'A'
--Union all Select '2012-01-02',N'#2',N'A'
--Union all Select '2012-01-03',N'#3',N'A'
--Union all Select '2012-01-04',N'#4',N'A'
--Union all Select '2012-02-01',N'#5',N'A'
--Union all Select '2012-02-02',N'#6',N'B'
--Union all Select '2012-02-03',N'#7',N'B'
--Union all Select '2012-05-14',N'#8',N'B'
--Union all Select '2012-05-16',N'#9',N'C'
--Union all Select '2012-05-18',N'#10',N'B'
--Union all Select '2012-05-19',N'#11',N'C'
--Union all Select '2012-09-05',N'#12',N'A'
--Union all Select '2012-09-06',N'#13',N'D'
--Union all Select '2012-09-07',N'#14',N'D'
SELECT
COUNT(a.日期) AS [Count]
,b.[Week]
,b.[Year]
FROM tb AS a
RIGHT JOIN (
SELECT * FROM (
SELECT
YEAR(日期) AS [Year]
FROM tb
GROUP BY YEAR(日期)
) t,(
SELECT TOP 52
ROW_NUMBER()OVER(ORDER BY getdate()) AS [Week]
FROM sys.syscolumns
) t2 ) AS b ON YEAR(a.日期)=b.Year AND DATEPART(week,a.日期)=b.Week
GROUP BY b.[Week],b.[Year]
/*
Count Week Year
------ ------ -----
4 1 2012
0 2 2012
0 3 2012
0 4 2012
3 5 2012
0 6 2012
0 7 2012
0 8 2012
0 9 2012
0 10 2012
0 11 2012
0 12 2012
0 13 2012
0 14 2012
0 15 2012
0 16 2012
0 17 2012
0 18 2012
0 19 2012
4 20 2012
0 21 2012
0 22 2012
0 23 2012
0 24 2012
0 25 2012
0 26 2012
0 27 2012
0 28 2012
0 29 2012
0 30 2012
0 31 2012
0 32 2012
0 33 2012
0 34 2012
0 35 2012
3 36 2012
0 37 2012
0 38 2012
0 39 2012
0 40 2012
0 41 2012
0 42 2012
0 43 2012
0 44 2012
0 45 2012
0 46 2012
0 47 2012
0 48 2012
0 49 2012
0 50 2012
0 51 2012
0 52 2012
*/
GO
---->生成表tb
--if object_id('tb') is not null
-- drop table tb
--Go
---- test data
--Create table tb([日期] datetime,[OrderNr] nvarchar(3),[CustomerName] nvarchar(1))
--Insert into tb
--Select '2012-01-01',N'#1',N'A'
--Union all Select '2012-01-02',N'#2',N'A'
--Union all Select '2012-01-03',N'#3',N'A'
--Union all Select '2012-01-04',N'#4',N'A'
--Union all Select '2012-02-01',N'#5',N'A'
--Union all Select '2012-02-02',N'#6',N'B'
--Union all Select '2012-02-03',N'#7',N'B'
--Union all Select '2012-05-14',N'#8',N'B'
--Union all Select '2012-05-16',N'#9',N'C'
--Union all Select '2012-05-18',N'#10',N'B'
--Union all Select '2012-05-19',N'#11',N'C'
--Union all Select '2012-09-05',N'#12',N'A'
--Union all Select '2012-09-06',N'#13',N'D'
--Union all Select '2012-09-07',N'#14',N'D'
--Union all Select '2013-01-06',N'#14',N'D'
--Union all Select '2013-01-07',N'#14',N'D'
;WITH Result AS (
SELECT YEAR(日期) AS [Year],DATEPART(Week,日期) AS [Week],* FROM tb
)
SELECT
COUNT(a.日期) AS [Count]
,b.[Week]
,b.[Year]
FROM Result AS a
RIGHT JOIN (
SELECT
t.Year,t2.Week
FROM (
SELECT DISTINCT
Year
FROM Result
) t,(
SELECT TOP 52
ROW_NUMBER()OVER(ORDER BY getdate()) AS [Week]
FROM sys.syscolumns
) t2
WHERE t2.Week>=(SELECT MIN(Week) FROM Result AS x WHERE x.Year=t.Year)
AND t2.Week<=(SELECT MAX(Week) FROM Result AS x WHERE x.Year=t.Year)
) AS b ON a.Year=b.Year AND a.Week=b.Week
GROUP BY b.[Week],b.[Year]
ORDER BY b.[Year],b.[Week]
/*
Count Week Year
------ ----- -----
4 1 2012
0 2 2012
0 3 2012
0 4 2012
3 5 2012
0 6 2012
0 7 2012
0 8 2012
0 9 2012
0 10 2012
0 11 2012
0 12 2012
0 13 2012
0 14 2012
0 15 2012
0 16 2012
0 17 2012
0 18 2012
0 19 2012
4 20 2012
0 21 2012
0 22 2012
0 23 2012
0 24 2012
0 25 2012
0 26 2012
0 27 2012
0 28 2012
0 29 2012
0 30 2012
0 31 2012
0 32 2012
0 33 2012
0 34 2012
0 35 2012
3 36 2012
2 2 2013
*/