javajohn
          

          金色年華

          

          

          
	
					
	
          
	
		
          
			ORACLE門事件
		
          
		
          
		
          
				
				
				
				
						/*
						?Formatted?on?2008/05/31?12:29?(Formatter?Plus?v4.8.8)?
						*/
				
				
						
          
						MERGE?          
				INTO
				?copy_emp?a
          ???USING?employees?b
          ???          
				ON
				?(b.employee_id?
				=
				?a.employee_id)
          ???          
				WHEN
				?MATCHED?
				THEN
				
						
          
						??????          
				UPDATE
				
						
          
						?????????          
				SET
				?a.employee_id?
				=
				?b.employee_id,?a.first_name?
				=
				?b.first_name,
          ?????????????a.last_name?          
				=
				?b.last_name,?a.email?
				=
				?b.email,
          ?????????????a.phone_number?          
				=
				?b.phone_number,?a.hire_date?
				=
				?b.hire_date,
          ?????????????a.job_id?          
				=
				?b.job_id,?a.salary?
				=
				?b.salary,
          ?????????????a.commission_pct?          
				=
				?b.commission_pct,?a.manager_id?
				=
				?b.manager_id,
          ?????????????a.department_id?          
				=
				?b.department_id
          ???          
				WHEN
				?
				NOT
				?MATCHED?
				THEN
				
						
          
						??????          
				INSERT
				
						
          
						??????          
				VALUES
				?(b.employee_id,?b.first_name,?b.last_name,?b.email,
          ??????????????b.phone_number,?b.hire_date,?b.job_id,?b.salary,
          ??????????????b.commission_pct,?b.manager_id,?b.department_id);          
		
          
		
          ?
          
		
          
				
				故事就這樣發生了：
          
				1
				.刪除重復記錄
          查出重復記錄
          SQL          
				>
				?
				select
				?rowid,bm,mc?
				from
				?a?
				where
				?a.rowid
				!=
				(
				select
				?
				max
				(rowid)?
				from
				?a?b?
				where
				?a.bm
				=
				b.bm?
				and
				?a.mc
				=
				b.mc);
          刪除重復記錄
          SQL          
				>
				?
				delete
				?
				from
				?a?a?
				where
				?a.rowid
				!=
				(
				select
				?
				max
				(rowid)?
				from
				?a?b?
				where
				?a.bm
				=
				b.bm?
				and
				?a.mc
				=
				b.mc);
          
		
          
		
          
				
				查找重復記錄2：
				select
				?
				column
				?
				from
				?
				table
				?
				group
				?
				by
				?clolumn?
				having
				?
				count
				(
				*
				)
				>
				1
		
          
		
          
		
          
				關于 FROM a,b 和 a LEFT JOIN b 的區別
				
				
						
          
						
						
          給個通俗的解釋吧.?
          例表a?
          aid?adate?
          
				1
				?a1?
          
				2
				?a2?
          
				3
				?a3?
          表b?
          bid?bdate?
          
				1
				?b1?
          
				2
				?b2?
          
				4
				?b4?
          兩個表a,b相連接,要取出id相同的字段?
          select?          
				*
				?from?a?,b?where?a.aid?
				=
				?b.bid這是僅取出匹配的數據.?
          此時的取出的是:?
          
				1
				?a1?b1?
          
				2
				?a2?b2?
          那么left?join?指:?
          select?          
				*
				?from?a?left?join?b?on?a.aid?
				=
				?b.bid?
          首先取出a表中所有數據,然后再加上與a,b匹配的的數據?
          此時的取出的是:?
          
				1
				?a1?b1?
          
				2
				?a2?b2?
          
				3
				?a3?空字符?
          同樣的也有right?join?
          指的是首先取出b表中所有數據,然后再加上與a,b匹配的的數據?
          此時的取出的是:?
          
				1
				?a1?b1?
          
				2
				?a2?b2?
          
				4
				?空字符?b4

          left outer join=left join          
		
          
		
          
		
          獲取所有子節點
          SELECT?r_org.*,?dor.dealer_id
          ??          FROM?(SELECT?connect_by_root?org_id?AS?root_org_id,
          ???????????????connect_by_root?org_code?          AS?root_org_code,
          ???????????????connect_by_root?org_name?          AS?root_org_name,
          ???????????????org_id,
          ???????????????org_code,
          ???????????????org_name
          ??????????          FROM?tm_org
          ?????????START?          WITH?org_code?LIKE?'NSC%'
          ????????CONNECT?          BY?PRIOR?org_id?=?parent_org_id)?r_org,
          ???????tm_dealer_org_relation?dor
          ?          WHERE?r_org.org_id?=?dor.org_id
          
		
          
				
				sybase數據庫分頁sql：
          
				============================
				
						
          
						
				          
				set
				?
				rowcount
				?
				3
				(當前記錄數
				=
				當前頁碼
				*
				每頁顯示條數)
          
				select
				?id?
				into
				?#lishi_operation?
				from
				?operation?a?
				order
				?
				by
				?a.id?
				asc
				
						
          
						
				          
				set
				?
				rowcount
				?
				5
				(每頁顯示條數)
          
				select
				?
				*
				?
				from
				?operation?a?
				where
				?a.id?
				not
				?
				in
				(
				select
				?id?
				from
				?#lishi_operation)?
				order
				?
				by
				?a.id?
				asc
				
						
          
						
				          
				set
				?
				rowcount
				?
				0
				
						
          
						
				          
				drop
				?
				table
				?#lishi_operation
		
          

          
		
          
			posted on 2008-05-09 15:58 javajohn 閱讀(354) 評論(0)  編輯  收藏  所屬分類: 數據庫 
		
          
	
          
	
	





          
	    
    




          








          

				

          

          

	

          My Links
          

	

          Blog Stats
          
	
	
	


          常用鏈接
          


          留言簿(7)
          


		
          隨筆分類(36)
          
		
				
			
	
		
          隨筆檔案(39)
          
		
				
			
	
		
          classmate
          
		
				
			
	
		
          good blog
          
		
				
			
	
		
          企業管理網站
          
		
				
			
	
		
          好友
          
		
				
			
	
		
          站點收藏
          
		
				
			
	


          搜索
          



          最新評論
	
          

          
	
			
		

          


          閱讀排行榜
          



          評論排行榜
          





          



    







          
        
	




主站蜘蛛池模板：
团风县|
肇州县|
铜陵市|
腾冲县|
威信县|
山阴县|
安远县|
蒙自县|
玉林市|
涿鹿县|
古交市|
北宁市|
罗源县|
稷山县|
高雄县|
霍州市|
秀山|
新田县|
醴陵市|
扎赉特旗|
郁南县|
正蓝旗|
双峰县|
江山市|
临洮县|
台州市|
北流市|
安图县|
广州市|
高邑县|
宁海县|
阿坝县|
浮山县|
赣州市|
吉首市|
秦安县|
积石山|
察哈|
库车县|
大宁县|
镶黄旗|