mysql - 获得最高分和胜利次数

Question

这是我的数据库。每张照片都有一些要点，并且是由特定用户拍摄的。每张照片也被分配给该事件。我想找到每个事件得分最高的图片。我还想计算您赢得了多少场比赛(例如，对于 id = 10 的用户)。请你帮助我好吗？我不知道如何解决这个问题。

Answer 1

这里有两个查询可以实现您的需求:

picture with the highest number of points for each event.

SELECT      e.id event_id,
            e.name event_name, 
            ph.id photo_id,
            ph.title photo_title,
            u.id user_id,
            u.login user_login,
            COUNT(*) points
FROM        events e
INNER JOIN  photos ph
        ON  ph.event_id = e.id
        AND ph.id = (
              SELECT      ph.id
              FROM        photos ph
              INNER JOIN  points p 
                       ON p.photo_id = ph.id
              WHERE       ph.event_id = e.id
              GROUP BY    ph.id
              ORDER BY    COUNT(*) DESC
              LIMIT       1
            )
-- optional if you need to know the points            
INNER JOIN  points p 
         ON p.photo_id = ph.id
-- optional if you need to know the owner of the photo
INNER JOIN  users u
         ON u.id = ph.user_id
GROUP BY    e.id,
            e.name,
            ph.id,
            ph.title

参见SQL fiddle .

count how many events you won

SELECT      u.id user_id,
            u.login user_login,
            COUNT(distinct e.id) events_won
FROM        events e
INNER JOIN  photos ph
        ON  ph.event_id = e.id
        AND ph.id = (
              SELECT      ph.id
              FROM        photos ph
              INNER JOIN  points p 
                       ON p.photo_id = ph.id
              WHERE       ph.event_id = e.id
              GROUP BY    ph.id
              ORDER BY    COUNT(*) DESC
              LIMIT       1
            )
INNER JOIN  users u
         ON u.id = ph.user_id
GROUP BY    u.id,
            u.login

参见SQL fiddle