sql - MySQL按最大值分组

Question

示例表(many2many = users has many tickers and tickers has many users):

#users
id     relevance
1      10           
2      6           
3      8           
4      3
5      5

#users_tickers
user_id     ticker_id
1           2
1           3
2           4
2           1
2           3
3           2
4           2
...

我必须为每个代码选择具有最大相关性的用户 - 因此对于每个代码一个具有最佳相关性的用户。你会怎么做？

Answer 1

应该这样做:

SELECT FROM users u 
INNER JOIN users_tickers ut ON ut.user_id=u.id
WHERE NOT EXISTS(
    SELECT FROM users u1 
    INNER JOIN users_tickers ut1 ON ut1.user_id=u1.id 
    WHERE ut1.ticker_id=ut.ticker_id AND u1.relevance > u.relevance
)