求存储过程，有几个问题请教
请教一下如何删除表中重复的记录，写出存储过程/sql语句也行
CARD_CODE Q BAL
1 1 27
1 2 10
1 3 36
1 4 97
2 1 96
2 2 12
2 3 15
2 4 32
1 1 27
1 2 10
1 3 36
1 4 97
2 1 96
2 2 12
2 3 15
2 4 32
create table card_code(
card_code integer,
Q integer,
BAL integer);
insert into card_code values(001,1,27);
insert into card_code values(001,2,10);
insert into card_code values(001,3,36);
insert into card_code values(001,4,97);
insert into card_code values(002,1,96);
insert into card_code values(002,2,12);
insert into card_code values(002,3,15);
insert into card_code values(002,4,32);
还有一个问题就是上表（删除重复后的表）变成这样的格式
CARD_CODE Q1 Q2 Q3 Q4
--------- ---------- ---------- ---------- ----------
001 27 10 36 97
002 96 12 15 32
存储在另一个表change中，求存储过程
[解决办法]
第一个问题：
delete from table1 d where rowid !=(
select max(rowid) from table1 where card_code=d.card_code and q=d.q and bal=d.bal
)
[解决办法]
第一个是典型的处理重复记录的问题：
http://blog.csdn.net/precipitant/archive/2007/04/14/1564321.aspx
第二个是典型的行列转置的问题：
http://blog.csdn.net/precipitant/archive/2007/05/15/1609321.aspx
[解决办法]
第一个:
DELETE FROM TEST_B
WHERE ROWID IN (
SELECT MAX(ROWID) OVER (PARTITION BY CARD_CODE, Q) AS MRID
FROM TEST_B)
第二个:
INSERT INTO change
SELECT CARD_CODE, SUM(Q1) AS Q1, SUM(Q2) AS Q2, SUM(Q3) AS Q3, SUM(Q4) AS Q4
FROM (
SELECT CARD_CODE, DECODE(Q, 1, BAL, 0) AS Q1,
DECODE(Q, 2, BAL, 0) AS Q2,
DECODE(Q, 3, BAL, 0) AS Q3,
DECODE(Q, 4, BAL, 0) AS Q4
FROM TEST_B
)
GROUP BY CARD_CODE
[解决办法]
第1个好像有问题啊!
3个值都重复才是重复纪录
select CARD_CODE,Q,BAL,count(*) from card_code
group by CARD_CODE,Q,BAL
having count(*)> 1
//下面是删除
create table test
as
select CARD_CODE,Q,BAL,count(*) from card_code
group by CARD_CODE,Q,BAL
drop table card_code;
rename test to card_code