去除重复数据

[ruixj]

drop table tmp;
create table tmp( anchor NUMBER not NULL, url NUMBER NOT NULL,countall NUMBER NOT NULL, coutout NUMBER NOT NULL);

insert into tmp values(1, 2, 10, 5);
insert into tmp values(3, 2, 10, 5);
insert into tmp values (1, 4,10, 5);
insert into tmp values(3, 5, 10, 5);


select distinct t1.anchor, t2.anchor
from tmp t1, tmp t2
where t1.url = t2.url
and t1.anchor != t2.anchor;

结果是：
1 3
3 1

但是实际上1 3 , 3 1 其实应该是同一条记录，怎么能够合并这种情况呢？

[fxianxian]

楼主要的是这样的效果吗？

SQL> select max(decode(rn, 1, anchor)) anchor1,
  2         max(decode(rn, 2, anchor)) anchor2
  3    from (select anchor, rownum rn from (select distinct anchor from tmp))
  4  /
 
   ANCHOR1    ANCHOR2
---------- ----------
         1          3
 
SQL> 

[ruixj]

要将用有相同的url 的 anchor 组成一组。

比如在我的例子里面

insert into tmp values(1, 2, 10, 5);
insert into tmp values(3, 2, 10, 5);

anchor 1 和 3都拥有 2 这个url ,所以 1 3 是同一组的， 但是我的sql会出现两条记录 1 3 和 3 1

你写得sql里没有url相同这个限制。

[ruixj]

没有人回答啊？

[小灰狼W]

select wm_concat(anchor),url
from table1
group by url

[小灰狼W]

select wm_concat(distinct anchor),url
from table1
group by url
having count(distinct anchor)>1

[ojuju10]

--是这个意思吗？
drop table tmp; 
create table tmp( anchor NUMBER not NULL, url NUMBER NOT NULL,countall NUMBER NOT NULL, coutout NUMBER NOT NULL); 

insert into tmp values(1, 2, 10, 5); 
insert into tmp values(3, 2, 10, 5); 
insert into tmp values (1, 4,10, 5); 
insert into tmp values(3, 5, 10, 5); 

select url,wmsys.wm_concat(anchor) from tmp a
group by url

[ojuju10]

select url,wmsys.wm_concat(anchor) from tmp a
group by url
having count(url)>1

[iqlife]

wm_concat() 9I你就痛苦了,一直想用这个函数,都没有机会哦,

[Rachel]

米看懂！

[evilmars]

何必呢，把相同url的找出来然后根据你的要求做这种序列多好啊，把自己逼得那么苦