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);

?

故事就這樣發(fā)生了：

1 .刪除重復(fù)記錄

查出重復(fù)記錄

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);

刪除重復(fù)記錄

SQL > ? delete ? from ?a?a? where ?a.rowid != ( select ? max (rowid)? from ?a?b? where ?a.bm = b.bm? and ?a.mc = b.mc);

查找重復(fù)記錄2： select ? column ? from ? table ? group ? by ?clolumn? having ? count ( * ) > 1

關(guān)于 FROM a,b 和 a LEFT JOIN b 的區(qū)別

給個(gè)通俗的解釋吧.?

例表a?

aid?adate?

1 ?a1?

2 ?a2?

3 ?a3?

表b?

bid?bdate?

1 ?b1?

2 ?b2?

4 ?b4?

兩個(gè)表a,b相連接,要取出id相同的字段?

select? * ?from?a?,b?where?a.aid? = ?b.bid這是僅取出匹配的數(shù)據(jù).?

此時(shí)的取出的是:?

1 ?a1?b1?

2 ?a2?b2?

那么left?join?指:?

select? * ?from?a?left?join?b?on?a.aid? = ?b.bid?

首先取出a表中所有數(shù)據(jù),然后再加上與a,b匹配的的數(shù)據(jù)?

此時(shí)的取出的是:?

1 ?a1?b1?

2 ?a2?b2?

3 ?a3?空字符?

同樣的也有right?join?

指的是首先取出b表中所有數(shù)據(jù),然后再加上與a,b匹配的的數(shù)據(jù)?

此時(shí)的取出的是:?

1 ?a1?b1?

2 ?a2?b2?

4 ?空字符?b4

left outer join=left join

獲取所有子節(jié)點(diǎn)

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數(shù)據(jù)庫分頁sql：

============================

set ? rowcount ? 3 (當(dāng)前記錄數(shù) = 當(dāng)前頁碼 * 每頁顯示條數(shù))

select ?id? into ?#lishi_operation? from ?operation?a? order ? by ?a.id? asc

set ? rowcount ? 5 (每頁顯示條數(shù))

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 閱讀(359) 評(píng)論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: sybase12.5 dsedit 找不到libtcl.dll錯(cuò)誤處理辦法 ORACLE門事件 apache dbcp數(shù)據(jù)庫連接池的使用 Oracle新手指南

My Links

Blog Stats

隨筆 - 39
文章 - 3
評(píng)論 - 71
Trackbacks - 0

常用鏈接

留言簿(7)

隨筆分類(36)

隨筆檔案(39)

javajohn

ORACLE門事件

My Links

Blog Stats

常用鏈接

留言簿(7)

隨筆分類(36)

隨筆檔案(39)

classmate

good blog

企業(yè)管理網(wǎng)站

好友

站點(diǎn)收藏

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜