select distinct 表2.新得意先CD,表1.商品CD from 表1,表2 where 表1.得意先CD=表2.旧得意先CD;
select distinct 表2.新得意先CD,表1.商品CD from 表1,表2 where 表1.得意先CD=表2.旧得意先CD group by 表1.得意先CD,表1.商品CD
谢谢以上几位,但还有问题-- Created on 2003/06/24 by ADMINISTRATOR declare -- Local variables here CURSOR TEST_CUR IS SELECT DISTINCT B.NEWDYXCD , A.DYXCD ,A.DPCD ,A.SPCD FROM TEST_OLD_DJ A ,TEST_BH B WHERE A.DYXCD= B.OLDDYXCD(+) ORDER BY A.DYXCD;
TEST_REG TEST_CUR%ROWTYPE;
begin OPEN TEST_CUR; LOOP FETCH TEST_CUR INTO TEST_REG;
EXIT WHEN TEST_CUR%NOTFOUND;
BEGIN INSERT INTO TEST_NEWTBL(NEWDYXCD ,SPCD ) VALUES (TEST_REG.NEWDYXCD ,TEST_REG.SPCD ); END; END LOOP;
CURSOR TEST_CUR IS SELECT B.NEWDYXCD ,max(A.DYXCD) DYXCD ,max(A.DPCD) DPCD ,max(A.SPCD) SPCD FROM TEST_OLD_DJ A ,TEST_BH B WHERE A.DYXCD= B.OLDDYXCD(+) group by B.NEWDYXCD ORDER BY A.DYXCD;
CURSOR TEST_CUR IS SELECT B.NEWDYXCD ,A.DYXCD ,max(A.DPCD) DPCD ,max(A.SPCD) SPCD FROM TEST_OLD_DJ A ,TEST_BH B WHERE A.DYXCD= B.OLDDYXCD(+) group by B.NEWDYXCD,A.DYXCD ORDER BY A.DYXCD;
select 表2.新得意先CD,表1.商品CD from 表1,表2 where 表1.得意先CD=表2.旧得意先CD group by 表1.得意先CD,表1.商品CD
这样写sql语句取得的数据多了两条,有重复的,注意表1的第4、5条记录
declare
-- Local variables here
CURSOR TEST_CUR IS
SELECT DISTINCT B.NEWDYXCD
, A.DYXCD
,A.DPCD
,A.SPCD
FROM TEST_OLD_DJ A
,TEST_BH B
WHERE A.DYXCD= B.OLDDYXCD(+)
ORDER BY A.DYXCD;
TEST_REG TEST_CUR%ROWTYPE;
begin OPEN TEST_CUR;
LOOP
FETCH TEST_CUR INTO TEST_REG;
EXIT WHEN TEST_CUR%NOTFOUND;
BEGIN
INSERT INTO TEST_NEWTBL(NEWDYXCD
,SPCD
)
VALUES (TEST_REG.NEWDYXCD
,TEST_REG.SPCD
); END;
END LOOP;
CLOSE TEST_CUR;
COMMIT;
end;----------------------------
在往新表插入数据时为什么是12条?即重复的纪录也被插入了!
SELECT B.NEWDYXCD
,max(A.DYXCD) DYXCD
,max(A.DPCD) DPCD
,max(A.SPCD) SPCD
FROM TEST_OLD_DJ A
,TEST_BH B
WHERE A.DYXCD= B.OLDDYXCD(+)
group by B.NEWDYXCD
ORDER BY A.DYXCD;
SELECT B.NEWDYXCD
,A.DYXCD
,max(A.DPCD) DPCD
,max(A.SPCD) SPCD
FROM TEST_OLD_DJ A
,TEST_BH B
WHERE A.DYXCD= B.OLDDYXCD(+)
group by B.NEWDYXCD,A.DYXCD
ORDER BY A.DYXCD;