select user1,user2,count(1) from userMessageTab where user1<>user2 group by user1,user2
应该考虑顺序问题: select t.user01,t.user02,count(1) from (select decode(sign(user1-user2),1,user2,user1) user01, decode(sign(user1-user2),1,user1,user2) from userMessageTab) t group by t.user01,t.user02;
armu80830(此情可待), beckhambobo(beckham) : 你们的都不符合要求,你们的结果是 100---------200----------2 200---------100----------1 100---------300----------1 200---------300----------1 没有把100发给200和200发给100的的和平起来啊,搂住是要两个人之间发的无论user1发给user2还是user2发给user1的要并在一起! : bzszp(SongZip) 的没有测试,看不出结果是什么? 另外,如果需要得到前100名如何写? 用select user1,user2,count(*) from userMessageTab where user1<>user2 and rownum<=100 group by user1,uesr2 order by count(*) 得到的却是count(*)小于100的纪录,而非前100条纪录!!哎!·
select * from ( select t.user01,t.user02,count(1) from (select decode(sign(user1-user2),1,user2,user1) user01, decode(sign(user1-user2),1,user1,user2) from userMessageTab) t group by t.user01,t.user02 ) where rownum<=100;SQL> select * from tcn;COLC COLN ---------- ---------- 111 1 111 1 111 1 222 2 222 2 222 2 222 2 333 3已选择8行。SQL> select colc,count(1) from tcn group by colc order by count(1) desc;COLC COUNT(1) ---------- ---------- 222 4 111 3 333 1SQL> select * from ( 2 select colc,count(1) from tcn group by colc order by count(1) desc 3 ) 4 where rownum<=2;COLC COUNT(1) ---------- ---------- 222 4 111 3SQL>
修正了一下: select t.user01,t.user02,count(1) from (select decode(sign(user1-user2),1,user2,user1) user01, decode(sign(user1-user2),1,user1,user2) user02 from userMessageTab where user1<>user2) t group by t.user01,t.user02;测试: SQL> select * from tcc;COLC1 COLC2 ---------- ---------- 100 200 100 200 200 100 200 200 100 300SQL> select t.user01,t.user02,count(1) from 2 (select decode(sign(colc1-colc2),1,colc2,colc1) user01, 3 decode(sign(colc1-colc2),1,colc1,colc2) user02 from tcc) t 4 group by t.user01,t.user02;USER01 USER02 COUNT(1) ---------- ---------- ---------- 100 200 3 100 300 1 200 200 1SQL> select t.user01,t.user02,count(1) from 2 (select decode(sign(colc1-colc2),1,colc2,colc1) user01, 3 decode(sign(colc1-colc2),1,colc1,colc2) user02 from tcc 4 where colc1<>colc2) t 5 group by t.user01,t.user02;USER01 USER02 COUNT(1) ---------- ---------- ---------- 100 200 3 100 300 1SQL>
select LEAST(user1,user2),GREATEST(user1,user2),count(1) from userMessageTab where user1<>user2 group by LEAST(user1,user2),GREATEST(user1,user2)
select t.user01,t.user02,count(1) from
(select decode(sign(user1-user2),1,user2,user1) user01,
decode(sign(user1-user2),1,user1,user2) from userMessageTab) t
group by t.user01,t.user02;
你们的都不符合要求,你们的结果是
100---------200----------2
200---------100----------1
100---------300----------1
200---------300----------1
没有把100发给200和200发给100的的和平起来啊,搂住是要两个人之间发的无论user1发给user2还是user2发给user1的要并在一起!
: bzszp(SongZip) 的没有测试,看不出结果是什么?
另外,如果需要得到前100名如何写?
用select user1,user2,count(*) from userMessageTab where user1<>user2 and rownum<=100 group by user1,uesr2 order by count(*)
得到的却是count(*)小于100的纪录,而非前100条纪录!!哎!·
select t.user01,t.user02,count(1) from
(select decode(sign(user1-user2),1,user2,user1) user01,
decode(sign(user1-user2),1,user1,user2) from userMessageTab) t
group by t.user01,t.user02
)
where rownum<=100;SQL> select * from tcn;COLC COLN
---------- ----------
111 1
111 1
111 1
222 2
222 2
222 2
222 2
333 3已选择8行。SQL> select colc,count(1) from tcn group by colc order by count(1) desc;COLC COUNT(1)
---------- ----------
222 4
111 3
333 1SQL> select * from (
2 select colc,count(1) from tcn group by colc order by count(1) desc
3 )
4 where rownum<=2;COLC COUNT(1)
---------- ----------
222 4
111 3SQL>
select t.user01,t.user02,count(1) from
(select decode(sign(user1-user2),1,user2,user1) user01,
decode(sign(user1-user2),1,user1,user2) user02 from userMessageTab
where user1<>user2) t
group by t.user01,t.user02;测试:
SQL> select * from tcc;COLC1 COLC2
---------- ----------
100 200
100 200
200 100
200 200
100 300SQL> select t.user01,t.user02,count(1) from
2 (select decode(sign(colc1-colc2),1,colc2,colc1) user01,
3 decode(sign(colc1-colc2),1,colc1,colc2) user02 from tcc) t
4 group by t.user01,t.user02;USER01 USER02 COUNT(1)
---------- ---------- ----------
100 200 3
100 300 1
200 200 1SQL> select t.user01,t.user02,count(1) from
2 (select decode(sign(colc1-colc2),1,colc2,colc1) user01,
3 decode(sign(colc1-colc2),1,colc1,colc2) user02 from tcc
4 where colc1<>colc2) t
5 group by t.user01,t.user02;USER01 USER02 COUNT(1)
---------- ---------- ----------
100 200 3
100 300 1SQL>
bzszp(SongZip):应该是可以的,但是我测试时错误,不过相对较复杂一点!
大感谢!