作者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)