急求!!!!

yang_lawrence 2012-12-04 10:31:12
sqlserver数据库中怎样获取2012-10-10到2012-11-30的所有周,(并且显示是一年中的第几周)?急用!!!谢谢大家。
...全文
151 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
我腫了 2012-12-05
  • 打赏
  • 举报
回复
添加篩選:週期範圍
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
*/
我腫了 2012-12-05
  • 打赏
  • 举报
回复
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]
	
	
/*
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
*/
nyx_116 2012-12-05
  • 打赏
  • 举报
回复
顶!
yang_lawrence 2012-12-05
  • 打赏
  • 举报
回复
引用 9 楼 bbbbbben 的回复:
類似於這樣? SQL code?1select year(日期) as [Year],datepart(week,日期) as [Week],count(1) as [Count] from tb Group by year(日期),datepart(week,日期)
嗯 类似于这样 但是这样只能查出有数据的,比如47周有数据,49周有数据,但是48周没有数据就不显示直接跳过48周了,我想即便没有数据也要显示出来并显示0.你明白我的意思吗
  • 打赏
  • 举报
回复
引用 7 楼 yang_lawrence 的回复:
引用 6 楼 TravyLee 的回复:SQL code?12345678910111213141516171819202122232425262728293031323334353637383940;with tas(select DATEADD(dd,number,'2012-10-10') dt, datepart(WK,DATEADD(dd,numb……
自己把你的原表的测试数据贴出来
yang_lawrence 2012-12-04
  • 打赏
  • 举报
回复
引用 6 楼 TravyLee 的回复:
SQL code?12345678910111213141516171819202122232425262728293031323334353637383940;with tas(select DATEADD(dd,number,'2012-10-10') dt, datepart(WK,DATEADD(dd,number,'2012-10-10')) a……
刚刚我试了一下 没有执行成功。我的要求就是把数据表里的数据从第一条数据的创建时间到最后一条数据的创建时间按照周度统计出来每周的数据量。如果有某一周没有数据的话就是0.有数据的话就是数据量。这样说你明白吗?
  • 打赏
  • 举报
回复
;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 行受影响)




*/
yang_lawrence 2012-12-04
  • 打赏
  • 举报
回复
引用 4 楼 TravyLee 的回复:
你给出测试数据和你想要的结果吧
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是第几周
  • 打赏
  • 举报
回复
你给出测试数据和你想要的结果吧
yang_lawrence 2012-12-04
  • 打赏
  • 举报
回复
引用 2 楼 TravyLee 的回复:
SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768select DATEADD(dd,number,'2012-10-10') dt, d……
我的表中的数据是从2012-10-10到2012-11-20的,请问应该怎么取,并返回某一周的数据数量?
  • 打赏
  • 举报
回复

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 行受影响)


*/
yang_lawrence 2012-12-04
  • 打赏
  • 举报
回复
没人吗?自己顶
-Tracy-McGrady- 2012-12-04
  • 打赏
  • 举报
回复
我腫了 2012-12-04
  • 打赏
  • 举报
回复
類似於這樣?
select year(日期) as [Year],datepart(week,日期) as [Week],count(1) as [Count] from tb Group by year(日期),datepart(week,日期)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧