该查询由 3 部分组成
- 用于计算每个用户的总事件的原始查询
- 对获得最多票数的所有活动进行排名。
- 过滤哪些事件具有最佳排名
在演示中,您还可以看到三个查询,以便您可以调试部分结果。
当前输出也是部分结果,为了获得您想要的结果,您需要添加
WHERE R.event_uid IS NULL
SQL DEMO http://sqlfiddle.com/#!9/0c781f/31
最终版本 http://sqlfiddle.com/#!9/0c781f/33
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
OUTPUT
| 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 |