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

          

          
	
          
		
          
			
 
BlogJava ::
首頁 ::
新隨筆 ::
聯系 ::

 ::
管理 ::


			
			
				

          
	
          
		
          
			
			
			
				 
				397 
				隨筆 ::
				33 文章
				::
				29 評論
				::
				0 Trackbacks
			
		
          
	
          

          

			          		
		
          
	
          

          


          


          
	
					

          
	
	
	
          
		
		
          
				
						SQL計算工作日
				
		
          
		
          
				
						
						
				?
          
		
          
				
						
						
				?
          
		
          
				
						??? 本文摘錄了兩段SQL的小技巧，來解決工作中的一些常用到的問題。主要是為了記錄一下第一條，這條SQL是用來計算兩個日期中的工作日的方法，從ITPUT那邊看來的，很實用的一段SQL，而且用的算法很好，值得學習。當然只能剔除掉周六周日，別的法定節假日肯定就不行了。另一個是寫的如何打印日歷，比較白癡的一段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')) 這句的作用是找出開始日為星期幾，并將其后的每一天對應011110，0為休息日，1為工作日
												
										
								
          
								
          
										
												
														
																3、rpad(substr('0111110',to_char(s,'d')),e-s+1,'0111110') 將字符串填充的e-s+1的長度，且每個工作日均對應為1，休息日為0
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										4、replace 的作用是把011111011111011111……中的0都刪除，留下1
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										5、最后用length 統計一下字符串的長度即為工作日的天數
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										6、這個方法雖然簡單，但是有一個很大的問題，就是Oracle的字符串最長只有4000個字符，也就是說，相隔時間大于4000天的，work_days 將恒等于2858而發生錯誤，所以需要進行進一步的改進
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
								
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
          
																																																																
																																																																		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的形式，然后進行不整位的補足，就沒有了字符串長度的限制
																																																												
          
																																																												
          
																																																												
          
																																																										          
																																																								          
																																																						          
																																																				          
																																																		          
																																																          
																																														          
																																												          
																																										          
																																								          
																																						          
																																				          
																																		          
																																          
																														          
																												          
																										          
																								          
																						          
																				          
																		          
																          
														          
												          
										          
								          
								
										
												2、當(e-s+1)是7的倍數時，顯然工作日就是 ((e-s+1)/7)*5
										
								
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						3、當(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)就是對mod(e-s+1,7)部分的操作
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								5、接下來這部分跟前一種方法的原理差不多，就是根據起始位置是星期幾，來順次往后取到工作日標記為1，休息日標記為0
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										
																																																																												
																																																																														
																																																																																
																																																																																		
																																																																																				
																																																																																						
																																																																																								
																																																																																										
																																																																																												
																																																																																														
																																																																																																
																																																																																																		
																																																																																																				
																																																																																																						
																																																																																																								
																																																																																																										
																																																																																																												
																																																																																																														
																																																																																																																
																																																																																																																		
																																																																																																																				
																																																																																																																						
																																																																																																																								
																																																																																																																										
																																																																																																																												
																																																																																																																														
																																																																																																																																
																																																																																																																																		
																																																																																																																																				
																																																																																																																																						6、需要注意的是substr('0111110',n,0)結果為null，replace('0','0','')的結果也為null，此時整個修正不為結果為null，無法正確顯示結果，好在這兩種情況下后面部分的結果都是可以忽略不記的，所以在最外面套一層nvl就可以了。
																																																																																																																																				
																																																																																																																																		
																																																																																																																																
																																																																																																																														
																																																																																																																												
																																																																																																																										
																																																																																																																								
																																																																																																																						
																																																																																																																				
																																																																																																																		
																																																																																																																
																																																																																																														
																																																																																																												
																																																																																																										
																																																																																																								
																																																																																																						
																																																																																																				
																																																																																																		
																																																																																																
																																																																																														
																																																																																												
																																																																																										
																																																																																								
																																																																																						
																																																																																				
																																																																																		
																																																																																
																																																																														
																																																																												
																																																																										
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										
								
          
								
          
										
												
														
																
																		
																				
																						
																								
																										
																												
																														
																																
																																		
																																				
																																						
																																								
																																										
																																												
																																														
																																																
																																																		
																																																				
																																																						
																																																								
																																																										
																																																												
																																																														
																																																																
																																																																		
																																																																				
																																																																						
																																																																								
																																																																										
																																																																												
																																																																														
																																																																																
																																																																																		
																																																																																				
																																																																																						
																																																																																								
																																																																																										
																																																																																												
																																																																																														
																																																																																																
																																																																																																		
																																																																																																				
																																																																																																						
																																																																																																								
																																																																																																										
																																																																																																												
																																																																																																														
																																																																																																																
																																																																																																																		
																																																																																																																				
																																																																																																																						
																																																																																																																								
																																																																																																																										
																																																																																																																												
																																																																																																																														
																																																																																																																																
																																																																																																																																		
																																																																																																																																				
																																																																																																																																						
																																																																																																																																						
																																																																																																																																				
																																																																																																																																		
																																																																																																																																
																																																																																																																														
																																																																																																																												
																																																																																																																										
																																																																																																																								
																																																																																																																						
																																																																																																																				
																																																																																																																		
																																																																																																																
																																																																																																														
																																																																																																												
																																																																																																										
																																																																																																								
																																																																																																						
																																																																																																				
																																																																																																		
																																																																																																
																																																																																														
																																																																																												
																																																																																										
																																																																																								
																																																																																						
																																																																																				
																																																																																		
																																																																																
																																																																														
																																																																												
																																																																										
																																																																								
																																																																						
																																																																				
																																																																		
																																																																
																																																														
																																																												
																																																										
																																																								
																																																						
																																																				
																																																		
																																																
																																														
																																												
																																										
																																								
																																						
																																				
																																		
																																
																														
																												
																										
																								
																						
																				
																		
																
														
												
										?
          
						          
				          
		
          
		
          
				
						
						
				?
          
		
          
				
						
								2、打印一張當月的日歷：
						
				
		
          
		
          
				
						
						?
		
          
		
          
				
          
						
								
          
										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'
												) 來取出當月的天數序列
										
								
						
          
						
          
								
										
												
														2、再用 to_char(trunc(sysdate, 'mm'), 'd')-2 來提取當天是對應星期幾
										
								
						
          
						
          
								
										
												
														
																3、通過
																decode(
																mod
																(n, 
																7
																), 
																0
																, m, 
																null
																) 來確定該天打印在星期幾的列中，其實mod放到第2步做思路更加清晰
														
												
										
								
						
          
						
          
								
										
												
														
																
																		4、通過
																		group
																		
																		
																		by
																		 trunc(n / 
																		7
																		) 來將一個星期內的天數打印到一行中
																
														
												
										
								
						
          
						
          
								
										
												
														
																
																		
																				5、雖然比較簡單，而且也沒有什么實用價值，不過這個小程序還是有一些閃光點的，比如group by，記錄一下
																		
																
														
												
										
								
						
          
						
          
								
										
												
														
																
																		
																				
																				
																		
																
														
												
										
								?
          
						
          
								
										
												
														
																
																		
																				
																						
																						
																				
																		
																
														
												?
								
						
          
				
          
		
          

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

          







          
	    
    




          








          

				

          




    







          
        
	




主站蜘蛛池模板：
赫章县|
且末县|
鄂伦春自治旗|
郯城县|
明溪县|
黑河市|
信阳市|
科尔|
应城市|
丘北县|
双江|
富阳市|
昆明市|
平凉市|
南平市|
文水县|
宽甸|
屏边|
基隆市|
报价|
达州市|
垣曲县|
临漳县|
铅山县|
临汾市|
上林县|
隆化县|
丹凤县|
颍上县|
潞西市|
藁城市|
平和县|
福贡县|
彰化市|
东阳市|
武宁县|
常山县|
林州市|
浪卡子县|
玉龙|
盐山县|