作者Marchino (內獅)
看板Database
標題Re: [SQL ] 不認得外層的 Table欄位名稱
時間Wed Aug 23 18:38:29 2006
※ 引述《ppanerai (Need Some Time...)》之銘言：
: 有幾個問題要說一下
: ※ 引述《Marchino (內獅)》之銘言：
: : 資料庫為Oracle。
: : 有一個EMP_TABLE員工歷史紀錄檔,總筆數10萬筆,EMP_ID為員工ID
: : 現須逐筆為每個相同員工ID的Record欄加上Count, 只能使用一個SQL Script完成此工作.
: 應該稱為 SQL Statement
謝謝你糾正m(_ _)m
: : EMP_ID RECORD
: : A10000 1
: : A10000 2
: : A10000 3
: : A13001 1
: : A13001 2
: : A15001 1
: : :: :
: : ==
: 以下的statement是不是從哪參考得到的
: SELECT ROW_NUMBER() over
: (partition by EMP_ID order by EMP_ID) AS ID_RECORD,
: ROWID AS SUB_ROWID
: FROM EMP_TABLE D
: WHERE D.EMP_ID = A.EMP_ID
: 這一句我覺得很怪 ... 用法好像不太對
: 很想知道是怎麼寫出來的
: 可以麻煩說一下嗎 ^^
在 EMP_TABLE 有這兩欄,EMP_ID員工編號 和 RECORD_NO紀錄編號
EMP_ID RECORD_NO
A10000 (NULL)
A10000 (NULL)
A10000 (NULL)
A13001 (NULL)
A13001 (NULL)
A15001 (NULL)
:: :
:: :
原本我寫的Statement是這樣的(我已試過,在Oracle8i或9i都可以被順利執行)
SELECT EMP_ID,
ROW_NUMBER() over (partition by EMP_ID order by EMP_ID) AS REC_NO
FROM EMP_TABLE D
再加上ORDER BY EMP_ID,REC_NO 就會跑出我需要的結果。
EMP_ID REC_NO
A10000 1
A10000 2
A10000 3
A13001 1
A13001 2
A15001 1
:: :
:: :
===
也就是說我希每一筆Data都會被依序標號,
例如A15001有12筆,REC_NO就被順序編上1,2,3...,12
依此模式 Update EMP_TABLE 所有Data的RECORD_NO欄位(共約10萬筆Data)。
: 如果我想得沒錯
: 你應該是要這樣
: UPDATE EMP_TABLE A
: SET A.RECORD = ( SELECT CNT
: FROM (SELECT EMP_ID , COUNT(*) CNT
: FROM EMP_TABLE
: GROUP BY EMP_ID
: ) B
: WHERE B.EMP_ID = A.EMP_ID
: )
這樣不符合需求耶,執行的結果會變成這樣
EMP_ID RECORD
A10000 3
A10000 3
A10000 3
A13001 2
A13001 2
A15001 12
:: :
:: :
: 另外 有一點要特別說明
: 你的TABLE有個欄位叫 record
: 這是保留字
: 設計table時最好避免使用 reserved word
謝謝提醒,馬上改
: : UPDATE EMP_TABLE A
: : set A.RECORD = (
: : SELECT B.ID_RECORD
: : FROM (
: : SELECT ROW_NUMBER() over
: : (partition by EMP_ID order by EMP_ID) AS ID_RECORD,
: : ROWID AS SUB_ROWID
: : FROM EMP_TABLE D
: : WHERE D.EMP_ID = A.EMP_ID
: : ) B
: : WHERE B.SUB_ROWID=A.ROWID
: : )
: : 執行時出現 ORA-00904: "A"."EMP_ID": invalid identifier 的錯誤
: : 似乎不認悼~層的 Table欄位名稱,
: : 是我的語法錯誤嗎?
UPDATE EMP_TABLE A
set A.RECORD_NO = (
SELECT B.REC_NO
FROM (
SELECT ROW_NUMBER() over
(partition by EMP_ID order by EMP_ID) AS REC_NO,
ROWID AS SUB_ROWID
FROM EMP_TABLE D
WHERE D.EMP_ID = A.EMP_ID
) B
WHERE B.SUB_ROWID=A.ROWID
)
沒有黃色那行的限制條件，是可以順利執行，
但很要命的是執行時間過長，
假設EMP_TABLE僅有500筆,執行時間為0.5秒;
10萬筆的資料可能就需要約三四十萬秒
(我對BIG-O Notation 的概念模糊,錯了請糾正)
所以才加黃色那一行來增加效率
(ORA-00904: "A"."EMP_ID": invalid identifier........囧)
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 61.222.163.182
※ 編輯: Marchino 來自: 203.67.8.67 (08/23 20:14)