MYSQL 查询按 if 值分组的更好方法？

Question

我有一个有效的查询，但必须有更好的方法来执行此操作。这是工作查询

SELECT gameid FROM(
    SELECT gameid, SUM(count) as total FROM (
        SELECT IF(vanguard = 1, 30, gameid) as gameid, count FROM (
            SELECT opserv_operation.gameid, opserv_games.vanguard, COUNT(opserv_games.gameid) AS count FROM opserv_operation_attendees INNER JOIN opserv_operation ON opserv_operation_attendees.operationid = opserv_operation.operationid INNER JOIN opserv_games on opserv_operation.gameid = opserv_games.gameid WHERE (start_time >= '2015-11-11' || FIND_IN_SET(opserv_operation.operationid, '17951,17701,17702,17775,17969,17890,17958,17966,17900')) AND completed = 1 AND opserv_operation_attendees.userid = 5750 AND opserv_operation_attendees.status = 4 AND opserv_operation.type <> 5 AND opserv_operation.completed = 1 GROUP BY opserv_operation.gameid) as m
        ) as l 
    GROUP BY gameid
    ORDER BY total DESC LIMIT 1) as k

主要查询

SELECT opserv_operation.gameid, opserv_games.vanguard, COUNT(opserv_games.gameid) AS count FROM opserv_operation_attendees INNER JOIN opserv_operation ON opserv_operation_attendees.operationid = opserv_operation.operationid INNER JOIN opserv_games on opserv_operation.gameid = opserv_games.gameid WHERE (start_time >= '2015-11-11' || FIND_IN_SET(opserv_operation.operationid, '17951,17701,17702,17775,17969,17890,17958,17966,17900')) AND completed = 1 AND opserv_operation_attendees.userid = 5750 AND opserv_operation_attendees.status = 4 AND opserv_operation.type <> 5 AND opserv_operation.completed = 1 GROUP BY opserv_operation.gameid

游戏先锋
16 0
36 1
36 1
36 1
16 0
36 1
27 0
16 0
36 1
36 1
36 1
30 0
36 1
36 1
27 0
36 1
36 1
36 1

所以这是棘手的部分。我需要它来分组并计算所有值，但是如果 gameid = 30 或 vanguard = 1 那么它们应该一起计算。

因此，对于上述数据，我唯一需要的是在本例中返回的 gameid，即 gameid 30。它的计数最多，为 13。

一定有比我做的更好的方法。

编辑:到目前为止，由于 Ed Gibbs，这一个工作得很好。但是仍然返回两个值，我猜唯一的方法是有另一个子查询？只获取 gameid。

SELECT
CASE WHEN vanguard = 1 THEN 30 ELSE gameid END AS gameid,
SUM(count) AS total
FROM (SELECT opserv_operation.gameid, opserv_games.vanguard, COUNT(opserv_games.gameid) AS count FROM opserv_operation_attendees INNER JOIN opserv_operation ON opserv_operation_attendees.operationid = opserv_operation.operationid INNER JOIN opserv_games on opserv_operation.gameid = opserv_games.gameid WHERE (start_time >= '2015-11-11' || FIND_IN_SET(opserv_operation.operationid, '17951,17701,17702,17775,17969,17890,17958,17966,17900')) AND completed = 1 AND opserv_operation_attendees.userid = 5750 AND opserv_operation_attendees.status = 4 AND opserv_operation.type <> 5 AND opserv_operation.completed = 1 GROUP BY opserv_operation.gameid) as l
GROUP BY CASE WHEN vanguard = 1 THEN 30 ELSE gameid END
ORDER BY total DESC
LIMIT 1;

Answer 1

如果底线你的意思是“1”和“30”必须一起计算那么这应该有效:

SELECT
  CASE WHEN vanguard = 1 THEN 30 ELSE gameid END AS gameid
FROM (... main query ...)
GROUP BY CASE WHEN gameid = 1 THEN 30 ELSE gameid END
ORDER BY SUM(count) DESC
LIMIT 1;