MySQL 根据找到的 ID 返回 1 或 0

Question

以下是相关表格的片段:

用户

|  USER_UID  |  FIRSTNAME  |  LASTNAME  | 
    abc123         bob          smith
    def456         rob          smithies 
    ghi789         john         clark

事件

| GUID | NAME |  
  ev1   event1
  ev2   event2
  ev3   event3

USER_EVENT

| USER_EVENT_ID | USER_UID | EVENT_UID | 
       1            abc123     ev1
       2            def456     ev2
       3            ghi789     ev3

EVENT_VOTE

| EVENT_VOTE_ID | USER_UID | EVENT_UID | 
         1         def456      ev1       (user2 voted for user1's event)

我有以下查询，它返回事件以及投票数和基于此的排名:

SELECT t.*, @curRank := @curRank + 1 AS rank
FROM ( SELECT e.guid,
              e.name,
              ( SELECT COUNT(ev.event_vote_id) 
                FROM event_vote ev 
                WHERE ev.event_uid = e.guid
              ) AS votes
              FROM event e
      ) AS t
CROSS JOIN (SELECT @curRank := 0) r
ORDER BY votes DESC

<小时/>

如果用户没有投票资格，我还想返回 1 或 0。

如果出现以下情况，用户没有资格投票:

• 用户是事件的所有者。

• 用户已对该事件投票(用户 uid 和事件 uid 可在 event_vote 中找到)。

如果用户已经投票，但如果用户拥有该事件，则 ...AS 投票之后的以下代码会给出正确的响应。

,(
    SELECT COUNT(*)
    FROM event_vote ev
    WHERE ev.event_uid = e.guid
    AND ev.user_uid = '{$user_uid}'
 ) AS ineligible

预期结果(从用户 2 查看所有事件时的角度)

guid: ev1
name: event1
votes: 1 
rank: 1
ineligible: 1 (already voted).

guid: ev2
name: event2
votes: 0
rank: 2
ineligible: 1 (user owns this event)

guid: ev3
name: event3
votes: 0 
rank: 3
ineligible: 0 (user doesn't own this event and has yet to vote).

Answer 1

您可以将此查询与给出 1 或 null 的 case when 表达式结合使用。它聚合为 count(distinct ...)，仅当聚合值中至少有一个 1 时才给出 1，否则给出 0:

SELECT      t.*,
            @curRank := @curRank + 1 AS rank 
FROM        (
                SELECT     u.user_uid,
                           e.guid, 
                           e.name, 
                           count(ev.user_uid) votes, 
                           count(distinct 
                               case when u.user_uid in (ev.user_uid, ue.user_uid)
                                    then 1 
                               end) ineligible 
                FROM       user u
                CROSS JOIN event e
                INNER JOIN user_event ue
                        ON ue.event_uid = e.guid
                LEFT JOIN  event_vote ev
                        ON ev.event_uid = e.guid
                GROUP BY   u.user_uid, 
                           e.guid
                ORDER BY   votes desc,
                           e.guid,
                           u.user_uid
            ) as t
CROSS JOIN (SELECT @curRank := 0) r
WHERE      t.user_uid = 'def456'
ORDER BY   votes desc,
           guid, 
           user_uid

查看它在 rextester.com 上运行。

请注意，当您使用此类变量时，必须在内部查询中强制执行顺序。如果您只在外部查询上执行此操作，那么确实来得太晚了，因为变量表达式已经被求值了。虽然它可能经常起作用，因为优化器可能会考虑外部order by，但不能保证。