有一个表lr的的结构如下:
id sender_id reciver_id score
1 1 2 30
2 2 1 21
3 1 3 13
4 3 1 23
现在我想找出sender_id等于1的数据并按分数排序,不过这个分数是加上相对的数据,即是score = (sender_id = 1 and receiver_id = 3) 的SCORE + (sender_id = 3 and receiver_id = 1)的SCORE
上面是考虑了多一对的，这个是成对出现的
[code="sql"]
select * from(
select a.id,a.sender_id,a.reciver_id,a.score+b.score as score from lr a,lr b
where a.sender_id= b.reciver_id and a.reciver_id=b.sender_id ) t
where t.sender_id<t.reciver_id
[/code]
没看明白，把你想要的结果列出来
数据都是成对的出现吗，还是会出现1对多的情况，比如下面的情况：
id sender_id reciver_id score
1 1 2 30
2 2 1 21
3 1 3 13
4 3 1 23
5 4 1 10
结果就是：
sender_id receiver_id score
1 2 51
1 3 56
[code="sql"]
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
[/code]
我终于看到你的最后一句话。。。
想了半天那多出来的一对怎么办。。。。
[code="java"]
with lr as(
select 1 as id, 1 as sender_id,2 as reciver_id, 30 as score from dual
union
select 2 as id, 2 as sender_id,1 as reciver_id, 21 as score from dual
union
select 3 as id, 1 as sender_id,3 as reciver_id, 13 as score from dual
union
select 4 as id, 3 as sender_id,1 as reciver_id, 23 as score from dual
union
select 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 b
where a.sender_id= b.reciver_id(+) and a.reciver_id=b.sender_id(+) ) t
where t.sender_id<t.reciver_id
[/code]