MySQL返回用户排名最高的事件

Question

我目前使用以下查询来获取每个用户的详细信息。

SELECT u.*, sums.total_votes, sums.no_of_events
FROM user u
LEFT JOIN
(
  SELECT
    us.user_uid,
    count(ev.event_vote_id) AS total_votes 
    count(distinct ue.event_uid) AS no_of_events
  FROM user_event ue
  LEFT JOIN event_vote ev
  ON ev.event_uid = ue.event_uid
  GROUP BY ue.user_uid
) sums ON sums.user_uid = u.user_uid

但是，我还希望返回他们投票最高的事件的排名(在所有事件中 - 不仅仅是他们自己的事件)。

用户

|  USER_UID  |  FIRSTNAME  |  LASTNAME  | 
       1         bob          smith
       2         rob          smithies 
       3         john         clark

事件

| GUID | NAME |  
  101   event1
  102   event2
  103   event3

USER_EVENT

| USER_EVENT_ID | USER_UID | EVENT_UID | 
       1001           1         101
       1002           2         102
       1003           1         103

EVENT_VOTE

| EVENT_VOTE_ID | USER_UID | EVENT_UID | 
       2001            2       101       
       2002            3       101
       2003            2       103

预期结果

user_uid: 1
firstname: bob
lastname: smith
votes: 3        // 2 for 101, 1 for 103.
no_of_events: 2
bestRank: 1 (1st)    // ranked 1st and 2nd but 1st is higher.

user_uid: 2
firstname: rob
lastname: smithies
votes: 0      
no_of_events: 1
bestRank: 3 (3rd)

Answer 1

这个查询有3个部分

1. 用于计算每个用户的总事件的原始查询
2. 为大多数选票对所有事件进行排名。
3. 筛选排名最高的事件

在演示中您还可以看到三个查询，因此您可以调试部分结果。

当前输出也是部分结果，要得到你想要的结果你需要添加

WHERE R.event_uid IS NULL

SQL DEMO

Final version

SELECT *
FROM (  SELECT u.*, sums.total_votes, sums.no_of_events
        FROM user u
        JOIN ( SELECT ue.user_uid,
                      count(ev.event_vote_id) AS total_votes, 
                      count(distinct ue.event_uid) AS no_of_events
               FROM user_event ue
               LEFT JOIN event_vote ev
                 ON ev.event_uid = ue.event_uid
               GROUP BY ue.user_uid
             ) as sums
          ON u.user_uid  = sums.user_uid 
     ) as U
JOIN (  SELECT T.*,
               @rank := @rank + 1 as rn,
               @dense := if (@votes = votes,
                             @dense,
                             if(@votes := votes, @rank, @rank)
                            ) as dense
        FROM (
              SELECT 
                     e.guid as event_uid,
                     ue.user_uid, 
                     count(ev.event_uid) AS votes             
              FROM event e
              JOIN user_event ue
                ON e.guid = ue.event_uid
              LEFT JOIN event_vote ev
                ON ev.event_uid = ue.event_uid      
              GROUP BY e.GUID, ue.user_uid
              ORDER BY count(ue.event_uid) DESC
             ) as T
        CROSS JOIN (SELECT @rank := 0, @dense := 0, @votes := 0 ) as vars
        ORDER BY votes desc, event_uid
)  as Q
ON U.user_uid = Q.user_uid
LEFT JOIN (  SELECT T.*,
               @rank2 := @rank2 + 1 as rn,
               @dense2 := if (@votes2 = votes,
                             @dense2,
                             if(@votes2 := votes, @rank2, @rank2)
                            ) as dense
        FROM (
              SELECT 
                     e.guid as event_uid,
                     ue.user_uid, 
                     count(ev.event_uid) AS votes             
              FROM event e
              JOIN user_event ue
                ON e.guid = ue.event_uid
              LEFT JOIN event_vote ev
                ON ev.event_uid = ue.event_uid      
              GROUP BY e.GUID, ue.user_uid
              ORDER BY count(ue.event_uid) DESC
             ) as T
        CROSS JOIN (SELECT @rank2 := 0, @dense2 := 0, @votes2 := 0 ) as vars
        ORDER BY votes desc, event_uid
)  as R
ON  Q.user_uid = R.user_uid
AND Q.rn > R.rn
-- WHERE  R.event_uid IS NULL

输出

| USER_UID | FIRSTNAME | LASTNAME | total_votes | no_of_events | event_uid | user_uid | votes | rn | dense | event_uid | user_uid |  votes |     rn |  dense |
|----------|-----------|----------|-------------|--------------|-----------|----------|-------|----|-------|-----------|----------|--------|--------|--------|
|        1 |       bob |    smith |           3 |            2 |       101 |        1 |     2 |  1 |     1 |    (null) |   (null) | (null) | (null) | (null) |
|        1 |       bob |    smith |           3 |            2 |       103 |        1 |     1 |  3 |     2 |       101 |        1 |      2 |      1 |      1 |
|        2 |       rob | smithies |           1 |            3 |       102 |        2 |     1 |  2 |     2 |    (null) |   (null) | (null) | (null) | (null) |
|        2 |       rob | smithies |           1 |            3 |       104 |        2 |     0 |  4 |     4 |       102 |        2 |      1 |      2 |      2 |
|        2 |       rob | smithies |           1 |            3 |       105 |        2 |     0 |  5 |     4 |       102 |        2 |      1 |      2 |      2 |
|        2 |       rob | smithies |           1 |            3 |       105 |        2 |     0 |  5 |     4 |       104 |        2 |      0 |      4 |      4 |