Decode360's Blog
          
				
          業(yè)精于勤而荒于嬉    QQ:150355677    MSN:decode360@hotmail.com
          
			          		
		
          
	
          

          

          
	
          
		
          
			
 
BlogJava ::
首頁 ::
新隨筆 ::
聯(lián)系 ::

 ::
管理 ::


			
			
				

          
	
          
		
          
			
			
			
				 
				397 
				隨筆 ::
				33 文章
				::
				29 評(píng)論
				::
				0 Trackbacks
			
		
          
	
          

          

			          		
		
          
	
          

          


          


          
	
					

          
	
	
	
          
		
		
          
				
						SQL計(jì)算工作日
				
		
          
		
          
				
						
						
				?
          
		
          
				
						
						
				?
          
		
          
				
						??? 本文摘錄了兩段SQL的小技巧，來解決工作中的一些常用到的問題。主要是為了記錄一下第一條，這條SQL是用來計(jì)算兩個(gè)日期中的工作日的方法，從ITPUT那邊看來的，很實(shí)用的一段SQL，而且用的算法很好，值得學(xué)習(xí)。當(dāng)然只能剔除掉周六周日，別的法定節(jié)假日肯定就不行了。另一個(gè)是寫的如何打印日歷，比較白癡的一段SQL，呵呵，順手摘錄一下。
				
		
          
		
          
				
						
						
				?
          
		
          
				
						
						
				?
          
		
          
				
						
								
          
										
												
														
																
																		
																				
																						
																								
																										1、用SQL直接取工作日：
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																						
																						
																						
																								
																								
																						
																				
																		
																
														
												
										
										
										?
          
								
										
												
														
																
																		
																				
																						
          
																								with
																								 t 
																								as
																								 (
																								select
																								
																								
																								date
																								'2008-09-01'
																								 s,
																								date
																								'2008-09-10'
																								 e 
																								from
																								 dual)
																						
          
																						
          
																								select
																								 s,e,e-s+
																								1
																								 total_days, 
																						
          
																						
          
																								(length(
																								replace
																								(rpad(substr(
																								'0111110'
																								,to_char(s,
																								'd'
																								)),e-s+
																								1
																								,
																								'0111110'
																								),
																								'0'
																								,
																								''
																								))) work_days 
																						
          
																						
          
																								from
																								 t;
																						
          
																						
          
																								
																								?
          
																						
          
																								說明：
																						
          
																						
          
																						
          
																				          
																		          
																          
														          
												          
										          
								          
								
										1、其他沒什么好說的，主要就是(length(replace(rpad(substr('0111110',to_char(s,'d')),e-s+1,'0111110'),'0',''))) 這句了，一層層看一下
								
								
          
										
												
														2、substr('0111110',to_char(s,'d')) 這句的作用是找出開始日為星期幾，并將其后的每一天對(duì)應(yīng)011110，0為休息日，1為工作日
												
										
								
          
								
          
										
												
														
																3、rpad(substr('0111110',to_char(s,'d')),e-s+1,'0111110') 將字符串填充的e-s+1的長度，且每個(gè)工作日均對(duì)應(yīng)為1，休息日為0
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										4、replace 的作用是把011111011111011111……中的0都刪除，留下1
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										5、最后用length 統(tǒng)計(jì)一下字符串的長度即為工作日的天數(shù)
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										6、這個(gè)方法雖然簡單，但是有一個(gè)很大的問題，就是Oracle的字符串最長只有4000個(gè)字符，也就是說，相隔時(shí)間大于4000天的，work_days 將恒等于2858而發(fā)生錯(cuò)誤，所以需要進(jìn)行進(jìn)一步的改進(jìn)
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
								
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
          
																																																																
																																																																		with
																																																																		 t 
																																																																		as
																																																																		 (selectdate'2008-09-01' s,date'2008-09-10' e from dual
																																																																		)
																																																																
																																																														
          
																																																														
          
																																																																
																																																																		select
																																																																		 s,e,e-s+
																																																																		1
																																																																		 total_days,
																																																																
																																																														
          
																																																														
          
																																																																
																																																																		trunc((e-s+
																																																																		1
																																																																		)/
																																																																		7
																																																																		)*
																																																																		5
																																																																		 + nvl(length(
																																																																		replace
																																																																		(substr(
																																																																		'01111100111110'
																																																																		,to_char(s,
																																																																		'd'
																																																																		),
																																																																		mod
																																																																		(e-s+
																																																																		1
																																																																		,
																																																																		7
																																																																		)),
																																																																		'0'
																																																																		,
																																																																		''
																																																																		)),
																																																																		0
																																																																		) work_days
																																																																		
																																																																				
																																																																				
																																																																		
																																																																
																																																														
          
																																																														
          
																																																																
																																																																		from
																																																																		 t;
																																																																
																																																														
          
																																																														
          
																																																																
																																																																
																																																														
          
																																																												          
																																																												
          
																																																														
																																																														?
          
																																																												
          
																																																														說明：
																																																												
          
																																																												
          
																																																														1、使用了trunc((e-s+1)/7)*5的形式，然后進(jìn)行不整位的補(bǔ)足，就沒有了字符串長度的限制
																																																												
          
																																																												
          
																																																												
          
																																																										          
																																																								          
																																																						          
																																																				          
																																																		          
																																																          
																																														          
																																												          
																																										          
																																								          
																																						          
																																				          
																																		          
																																          
																														          
																												          
																										          
																								          
																						          
																				          
																		          
																          
														          
												          
										          
								          
								
										
												2、當(dāng)(e-s+1)是7的倍數(shù)時(shí)，顯然工作日就是 ((e-s+1)/7)*5
										
								
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						3、當(dāng)(e-s+1)不被7整除是，可以理解為將最前面不能被7整除的部分mod(e-s+1,7)取出來，剩余的部分則正好為((e-s+1)/7)*5
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								4、nvl(length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')),0)就是對(duì)mod(e-s+1,7)部分的操作
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								5、接下來這部分跟前一種方法的原理差不多，就是根據(jù)起始位置是星期幾，來順次往后取到工作日標(biāo)記為1，休息日標(biāo)記為0
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										
																																																																												
																																																																														
																																																																																
																																																																																		
																																																																																				
																																																																																						
																																																																																								
																																																																																										
																																																																																												
																																																																																														
																																																																																																
																																																																																																		
																																																																																																				
																																																																																																						
																																																																																																								
																																																																																																										
																																																																																																												
																																																																																																														
																																																																																																																
																																																																																																																		
																																																																																																																				
																																																																																																																						
																																																																																																																								
																																																																																																																										
																																																																																																																												
																																																																																																																														
																																																																																																																																
																																																																																																																																		
																																																																																																																																				
																																																																																																																																						6、需要注意的是substr('0111110',n,0)結(jié)果為null，replace('0','0','')的結(jié)果也為null，此時(shí)整個(gè)修正不為結(jié)果為null，無法正確顯示結(jié)果，好在這兩種情況下后面部分的結(jié)果都是可以忽略不記的，所以在最外面套一層nvl就可以了。
																																																																																																																																				
																																																																																																																																		
																																																																																																																																
																																																																																																																														
																																																																																																																												
																																																																																																																										
																																																																																																																								
																																																																																																																						
																																																																																																																				
																																																																																																																		
																																																																																																																
																																																																																																														
																																																																																																												
																																																																																																										
																																																																																																								
																																																																																																						
																																																																																																				
																																																																																																		
																																																																																																
																																																																																														
																																																																																												
																																																																																										
																																																																																								
																																																																																						
																																																																																				
																																																																																		
																																																																																
																																																																														
																																																																												
																																																																										
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										
																																																																												
																																																																														
																																																																																
																																																																																		
																																																																																				
																																																																																						
																																																																																								
																																																																																										
																																																																																												
																																																																																														
																																																																																																
																																																																																																		
																																																																																																				
																																																																																																						
																																																																																																								
																																																																																																										
																																																																																																												
																																																																																																														
																																																																																																																
																																																																																																																		
																																																																																																																				
																																																																																																																						
																																																																																																																								
																																																																																																																										
																																																																																																																												
																																																																																																																														
																																																																																																																																
																																																																																																																																		
																																																																																																																																				
																																																																																																																																						
																																																																																																																																						
																																																																																																																																				
																																																																																																																																		
																																																																																																																																
																																																																																																																														
																																																																																																																												
																																																																																																																										
																																																																																																																								
																																																																																																																						
																																																																																																																				
																																																																																																																		
																																																																																																																
																																																																																																														
																																																																																																												
																																																																																																										
																																																																																																								
																																																																																																						
																																																																																																				
																																																																																																		
																																																																																																
																																																																																														
																																																																																												
																																																																																										
																																																																																								
																																																																																						
																																																																																				
																																																																																		
																																																																																
																																																																														
																																																																												
																																																																										
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
						          
				          
		
          
		
          
				
						
						
				?
          
		
          
				
						
								2、打印一張當(dāng)月的日歷：
						
				
		
          
		
          
				
						
						?
		
          
		
          
				
          
						
								
          
										select
										
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										0
										, m, 
										null
										)) 
										
												日
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										1
										, m, 
										null
										)) 
										
												一
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										2
										, m, 
										null
										)) 
										
												二
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										3
										, m, 
										null
										)) 
										
												三
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										4
										, m, 
										null
										)) 
										
												四
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										5
										, m, 
										null
										)) 
										
												五
												,
										
								
          
								
          
										
												?????? 
										
										max
										(decode(
										mod
										(n, 
										7
										), 
										6
										, m, 
										null
										)) 
										
												六
												
														
														
												
										
								
          
								
          
										
												? 
										
										from
										 (
										select
										
										
										rownum
										 m, 
										rownum
										 + to_char(trunc(
										sysdate
										, 
										'mm'
										), 
										'd'
										) - 
										2
										 n
								
          
								
          
										
												????????? 
										
										from
										 dual
								
          
								
          
										
												??????? 
										
										connect
										
										
										by
										
										
										rownum
										 < = to_char(last_day(
										sysdate
										), 
										'dd'
										))
								
          
								
          
										
												?
										
										group
										
										
										by
										 trunc(n / 
										7
										)
								
          
								
          
										
												?
										
										order
										
										
										by
										 trunc(n / 
										7
										);
								
          
								
          
								
          
						          
						
          
								
										
												
												
										
								?
          
						
          
								
										
												說明：
										
								
						
          
						
          
								
										
												1、首先通過
												connect
												
												
												by
												
												
												rownum
												 < = to_char(last_day(
												sysdate
												),
												'dd'
												) 來取出當(dāng)月的天數(shù)序列
										
								
						
          
						
          
								
										
												
														2、再用 to_char(trunc(sysdate, 'mm'), 'd')-2 來提取當(dāng)天是對(duì)應(yīng)星期幾
										
								
						
          
						
          
								
										
												
														
																3、通過
																decode(
																mod
																(n, 
																7
																), 
																0
																, m, 
																null
																) 來確定該天打印在星期幾的列中，其實(shí)mod放到第2步做思路更加清晰
														
												
										
								
						
          
						
          
								
										
												
														
																
																		4、通過
																		group
																		
																		
																		by
																		 trunc(n / 
																		7
																		) 來將一個(gè)星期內(nèi)的天數(shù)打印到一行中
																
														
												
										
								
						
          
						
          
								
										
												
														
																
																		
																				5、雖然比較簡單，而且也沒有什么實(shí)用價(jià)值，不過這個(gè)小程序還是有一些閃光點(diǎn)的，比如group by，記錄一下
																		
																
														
												
										
								
						
          
						
          
								
										
												
														
																
																		
																				
																				
																		
																
														
												
										
								?
          
						
          
								
										
												
														
																
																		
																				
																						
																						
																				
																		
																
														
												?
								
						
          
				
          
		
          

	
          
	
	
          
		posted on 2008-08-30 19:21 decode360 閱讀(2800) 評(píng)論(0)  編輯  收藏  所屬分類: 05.SQL 
	
          

          







          
	    
    




          








          

				

          




    







          
        
	




主站蜘蛛池模板：
元朗区|
昭觉县|
安西县|
承德县|
炉霍县|
顺昌县|
贞丰县|
宁海县|
津市市|
公主岭市|
玉林市|
新宾|
怀仁县|
兴城市|
漯河市|
栾川县|
三门峡市|
庐江县|
海城市|
乌什县|
长春市|
泽库县|
沐川县|
霍州市|
安宁市|
都昌县|
思南县|
洮南市|
左权县|
泰安市|
水富县|
友谊县|
安陆市|
措勤县|
无锡市|
兰溪市|
九江县|
南城县|
海宁市|
湖口县|
余姚市|