## 急求一条sql语句写法

fangshuguang 发布于 2016/09/23 08:34

from_uid 和 to_uid 去重只只保留他们match_rate 最高的

0

0

select t1.* from tb_party_light_both t1,
(
select distinct max(t1.id) id from tb_party_light_both t1,
(
select uid, max(match_rate) match_rate from
(
select from_uid uid, max(match_rate) match_rate from tb_party_light_both t group by t.from_uid
union
select to_uid uid, max(match_rate) match_rate from tb_party_light_both t group by t.to_uid
) t group by uid
) t2
where t1.from_uid=t2.uid or t1.to_uid=t2.uid and t1.match_rate=t2.match_rate
group by t2.uid
) t2
where t1.id=t2.id

f

f

f

0

1.A给B亮灯100次，B给A亮灯90

2.A给C亮灯90，C给A亮灯80

3.B给C亮灯80，C给B亮灯70

则数据库查询出来的记录应该是A、B，即应该是A和B上台领奖？

0

```SELECT DISTINCT(a.id) id,	a.from_uid,	a.to_uid,	a.match_rate
FROM(
SELECT	uid,	max(match_rate) AS match_rate
FROM(
SELECT	from_uid uid,	match_rate	FROM	ft
UNION
SELECT	to_uid uid,	match_rate	FROM	ft
) AS t
GROUP BY
uid
HAVING
count(uid) > 1
) AS b,	ft AS a
WHERE
a.match_rate = b.match_rate AND (a.from_uid = b.uid OR a.to_uid = b.uid);```

0
select distinct
case when t1.uid<t1.max_match_uid then t1.uid else t1.max_match_uid end uid1,
case when t1.uid>t1.max_match_uid then t1.uid else t1.max_match_uid end uid1,
t1.max_rate
from
(
select t1.uid, case when t1.uid=t2.from_uid then t2.to_uid else t2.from_uid end max_match_uid, t1.max_rate from
(
select uid, max(max_rate) max_rate from
(
select from_uid uid, max(match_rate) max_rate FROM tb_party_light_both group by from_uid
UNION
select to_uid uid, max(match_rate) max_rate from tb_party_light_both group by to_uid
) t group by uid order by max_rate
) t1
join tb_party_light_both t2 on (t1.uid=t2.from_uid or t1.uid=t2.to_uid) and t1.max_rate=t2.match_rate
) t1,

(
select t1.uid, case when t1.uid=t2.from_uid then t2.to_uid else t2.from_uid end max_match_uid, t1.max_rate from
(
select uid, max(max_rate) max_rate from
(
select from_uid uid, max(match_rate) max_rate FROM tb_party_light_both group by from_uid
UNION
select to_uid uid, max(match_rate) max_rate from tb_party_light_both group by to_uid
) t group by uid order by max_rate
) t1
join tb_party_light_both t2 on (t1.uid=t2.from_uid or t1.uid=t2.to_uid) and t1.max_rate=t2.match_rate
) t2
where t1.max_rate=t2.max_rate and t1.uid=t2.max_match_uid and t1.max_match_uid=t2.uid
order by max_rate desc

0
f

from 和 to 是双向的，a可以亮b,b可以亮a, match数量在一个表中只是单向指数；

peopple a ; people b;

a_matchTo_b  指数c; b_match_a  指数d

final_match  最终指数e;

0
f

0
s
SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY from_uid,to_uid ORDER BY match_rate desc)rows FROM     XXXXXXX(表名)
) AS base WHERE rows=1