下在有下边一段的DB记录 no name addr tel back 1 kobe shenyang 6666 aa 2 kobe shenyang 6666 bb 3 kobe shenyang 6666 cc 4 jordan dalian 7777 ffff 5 jordan dalian 7777 asdfas 6 jordan dalian 7777 asdf 7 Yaom china 11 pp 8 Yaom china 11 asdf 9 Yaom china 11 oo 如果按照 要求的话 当 name、addr 、 tel 三个字段的数据相同取第一条记录这个要求的话 我最后应该得到的数据就是 1 kobe shenyang 6666 aa 4 jordan dalian 7777 ffff 7 Yaom china 11 pp |
SQL> select no, name, addr, tel, back
2 from (select no,
3 name,
4 addr,
5 tel,
6 back,
7 row_number() over(partition by name, addr, tel order by no) rn
8 from nba)
9 where rn < 2 order by no;
参考链接:http://www.itpub.net/thread-926220-1-1.html