如: 表ABC 到 表BCD (假设字段一样)ABC 中, a,b,c 三个字段
a b c
zhangsan 100 1
zhangsan 80 2
lisi 99 3
wangwu 77 4
wangwu 79 5
-- -- --怎样把zhangsan lisi wangwu 各插入一次到BCD表中?
a b c
zhangsan 100 1
zhangsan 80 2
lisi 99 3
wangwu 77 4
wangwu 79 5
-- -- --怎样把zhangsan lisi wangwu 各插入一次到BCD表中?
select a, b, c
from (
select a, b, c, row_number() over (partition by a order by b desc) rn
from abc
)
where rn = 1
select a, b, c
from (
select a, b, c, row_number() over (partition by a order by b desc) rn
from abc
)
where rn = 1
select a,b,c from
(select a,max(b),max(c) from abc group by a)
select a,b,c
from 表 a
where rowid != (select max(rowid)
from 表 b
where b.a = a.a )
select * from tb where rowid in (select max(rowid) from tb group by name having count(*)>1)
a b c
zhangsan sds sfd
zhangsan sds sds
lisi 99 3
wangwu 77 b
wangwu 79 b
herro erw 55
herro erw poi
-- -- -- 怎样把zhangsan lisi wangwu 各插入一次到BCD表中?
就是按姓名插入一次即可,不管数据是哪条。
insert into bcd
select a,b,c from abc where rowid in (select max(rowid) from abc group by a having count(*)>1);