当前位置: 代码迷 >> Oracle开发 >> 这段SQL 在Oracle 下能正确运行吗?该怎么处理
  详细解决方案

这段SQL 在Oracle 下能正确运行吗?该怎么处理

热度:29   发布时间:2016-04-24 07:41:37.0
这段SQL 在Oracle 下能正确运行吗?
SQL code
SELECT *,ID=IDENTITY(INT,1,1) INTO #FROM sch_resource_qualSELECT resource_code,qualification_no, dsp_seq=ID-(SELECT COUNT(*) FROM # WHERE resource_code<a.resource_code) INTO #1 FROM # AS A UPDATE  sch_resource_qual SET dsp_seq=#1.dsp_seq FROM sch_resource_qual,#1 WHERE sch_resource_qual.resource_code=#1.resource_code AND sch_resource_qual.qualification_no=#1.qualification_noDROP TABLE #DROP TABLE #1



------解决方案--------------------
SQL Server+ Linux ?
可以写成PL\SQL block的方式。
引用楼主 acidpassion 的帖子:
SQL code
SELECT *,ID=IDENTITY(INT,1,1) INTO #
FROM sch_resource_qual
SELECT resource_code,qualification_no, dsp_seq=ID-(SELECT COUNT(*) FROM # WHERE resource_code<a.resource_code) INTO #1 FROM # AS A
UPDATE sch_resource_qual SET dsp_seq=#1.dsp_seq FROM sch_resource_qual,#1 WHERE sch_resource_qual.resource_code=#1.resource_code
AND sch_resource_qual.qualification_no=#1.qualification_no
D…

------解决方案--------------------
要改成oracle的sql语句啊
------解决方案--------------------
不能在oracle下执行
可以先改成这样再执行:
create table tmp_1 as
SELECT a.*,rownum id
FROM sch_resource_qual a
/
create table tmp_2 as
SELECT resource_code,qualification_no, 
(
SELECT COUNT(*)-id
FROM tmp_1
WHERE resource_code<a.resource_code
) dsp_seq
FROM tmp_1 AS A 
/

UPDATE sch_resource_qual a SET dsp_seq=(
select b.dsp_seq from tmp_2 b
where a.resource_code=b.resource_code
and a.qualification_no=b.qualification_no
)
where exists (
select b.dsp_seq from tmp_2 b
where a.resource_code=b.resource_code
and a.qualification_no=b.qualification_no
)
/
commit
/
DROP TABLE tmp_1
/
DROP TABLE tmp_2
/
  相关解决方案