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 其实应该是同一条记录,怎么能够合并这种情况呢?
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 其实应该是同一条记录,怎么能够合并这种情况呢?
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>
insert into tmp values(3, 2, 10, 5); anchor 1 和 3都拥有 2 这个url ,所以 1 3 是同一组的, 但是我的sql会出现两条记录 1 3 和 3 1你写得sql里没有url相同这个限制。
from table1
group by url
from table1
group by url
having count(distinct anchor)>1
--是这个意思吗?
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
group by url
having count(url)>1