这段ＳＱＬ　在Oracle 下能正确运行吗？该怎么处理

这段ＳＱＬ　在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
/

查看更多 下一篇