oracle对合并字段去重,如何去重、合并 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区...

如下：

SQL> with t as(

2  select 123456 mobnum,'wang_wu' cname, 'Female' sex, null birthday, null work, 1 lvl from dual

3  union all

4  select 123456 mobnum,'wang_liu' cname,'Male' sex, '1987-1-3' birthday, null work, 2 lvl from dual

5  union all

6  select 123456 mobnum, null cname, null sex, null birthday,'Manager' work, 3 lvl from dual

7  union all

8  select 139888 mobnum,'SB2' cname, NULL sex, null birthday, 'Clerk' work, 1 lvl from dual

9  union all

10  select 139888 mobnum,'SB2' cname,'Male' sex, null birthday, 'Senior DBA' work, 2 lvl from dual

11  )

12  select  mobnum,

13          cname,

14          sex,

15          birthday,

16          work

17  from

18  (select mobnum,

19          last_value(cname ignore nulls) over(partition by mobnum order by lvl desc) cname,

20          last_value(sex ignore nulls) over(partition by mobnum order by lvl desc) sex,

21          last_value(birthday ignore nulls) over(partition by mobnum order by lvl desc) birthday,

22          last_value(work ignore nulls) over(partition by mobnum order by lvl desc) work,

23          lvl,

24          row_number() over(partition by mobnum order by lvl) rn

25    from t)

26  where rn = 1

27  /

MOBNUM CNAME    SEX    BIRTHDAY WORK

---------- -------- ------ -------- ----------

123456 wang_wu  Female 1987-1-3 Manager

139888 SB2      Male            Clerk

SQL>