数据库是Oracle的。
如: 表ABC 到 表BCD (假设字段一样)
ABC 中, a,b,c 三个字段
a b c
zhangsan sds sfd
zhangsan sds sfd
lisi 99 3
wangwu 77 b
wangwu 79 b
herro erw 55
herro erw poi
-- -- -- 怎样把zhangsan lisi wangwu 各插入一次到BCD表中?
就是按姓名插入一次即可,不管插入的数据是哪条。
BCD 表中的结果应该是 zhangsan,lisi,wangwu,herro 共4条
如: 表ABC 到 表BCD (假设字段一样)
ABC 中, a,b,c 三个字段
a b c
zhangsan sds sfd
zhangsan sds sfd
lisi 99 3
wangwu 77 b
wangwu 79 b
herro erw 55
herro erw poi
-- -- -- 怎样把zhangsan lisi wangwu 各插入一次到BCD表中?
就是按姓名插入一次即可,不管插入的数据是哪条。
BCD 表中的结果应该是 zhangsan,lisi,wangwu,herro 共4条
insert into BCD
SELECT * from ABC
where rowid in(
select max(rowid) from ABC group by a
)
SELECT * from ABC
where rowid in( select rd from
(select rowid rd,row_number() over(partition by a order by rowid) rn from ABC ) where rn=1
select a, b, c
from (select t.*, row_number() over(partition by t.a order by t.a) rn
from abc)
where rn = 1;
select a, b, c
from (select a, b, c, row_number() over(partition by a order by rownum) rn
from abc)
where rn = 1
------------------ -------- --------
zhangsan sds sfd
zhangsan sds sfd
lisi 99 3
wangwu 77 b
wangwu 79 b
herro erw 55
herro erw poi7 rows selectedSQL>
SQL> create table bcd as select * from dup_test where 1=0;Table createdSQL> select * from bcd;A B C
------------------ -------- --------SQL>
SQL> insert into bcd
2 select a, b, c
3 from (select a,
4 b,
5 c,
6 row_number() over(partition by a order by b desc) rn
7 from dup_test)
8 where rn = 1;4 rows insertedSQL> commit;Commit completeSQL> select * from bcd;A B C
------------------ -------- --------
herro erw 55
lisi 99 3
wangwu 79 b
zhangsan sds sfd--楼主最好在表内使用id,建立主关键字以区分不同的记录,否则以后麻烦太大。SQL>