请教一下如何删除表中重复的记录,写出存储过程/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 32create 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中,求存储过程
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 32create 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中,求存储过程
select max(rowid) from table1 where card_code=d.card_code and q=d.q and bal=d.bal
)
SUM(decode(q,3,bal,null)) Q3,SUM(decode(q,4,bal,null)) Q4
from CARD_CODE group by CARD_CODE
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
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,BALdrop table card_code;
rename test to card_code