问题描述
有一个表lr的的结构如下:id sender_id reciver_id score1 1 2 302 2 1 213 1 3 134 3 1 23现在我想找出sender_id等于1的数据并按分数排序,不过这个分数是加上相对的数据,即是score = (sender_id = 1 and receiver_id = 3) 的SCORE + (sender_id = 3 and receiver_id = 1)的SCORE 问题补充：我想得到的结果是sender_id receiver_id score1 2 511 3 46
解决方案
上面是考虑了多一对的，这个是成对出现的select * from(select a.id,a.sender_id,a.reciver_id,a.score+b.score as score from lr a,lr bwhere a.sender_id= b.reciver_id and a.reciver_id=b.sender_id ) t where t.sender_id<t.reciver_id
解决方案二：
我终于看到你的最后一句话。。。想了半天那多出来的一对怎么办。。。。with lr as(select 1 as id, 1 as sender_id,2 as reciver_id, 30 as score from dualunionselect 2 as id, 2 as sender_id,1 as reciver_id, 21 as score from dualunionselect 3 as id, 1 as sender_id,3 as reciver_id, 13 as score from dualunionselect 4 as id, 3 as sender_id,1 as reciver_id, 23 as score from dualunionselect 5 as id, 4 as sender_id,1 as reciver_id, 10 as score from dual) select * from(select a.id,a.sender_id,a.reciver_id,nvl(a.score,0)+nvl(b.score,0) as score from lr a,lr bwhere a.sender_id= b.reciver_id(+) and a.reciver_id=b.sender_id(+) ) t where t.sender_id<t.reciver_id
解决方案三：
SELECT n.sender_id, n.reciver_id, SUM(n.score) FROM (SELECT tmp.*, IF(@senderid = tmp.reciver_id, @groupid := @groupid, @groupid := tmp.id) AS group_num, @groupid := tmp.id, @senderid := tmp.sender_id FROM lr tmp, (SELECT @groupid := 0, @sum_over := 0, @senderid := 0) m) n GROUP BY n.group_num
解决方案四：
数据都是成对的出现吗，还是会出现1对多的情况，比如下面的情况：id sender_id reciver_id score 1 1 2 30 2 2 1 21 3 1 3 13 4 3 1 235 4 1 10结果就是：sender_id receiver_id score 1 2 51 1 3 56
解决方案五：
没看明白，把你想要的结果列出来