mysql - WHERE 子句中的多个 OR 条件 - 仅返回一条记录

Question

我有一个查询，其中根据 4 个条件为每个用户选择记录。我只想根据 heirachy 为每个用户返回这些记录中的一个 - 例如:如果满足第一个条件(例如 gemid = 123)，则不再包含该用户的任何记录.

更新 根据 Op De Cirkel 的评论，我想排除多个 subscribe, userid 对

查询:

SELECT subscribeid, userid
FROM subscribe 
WHERE (gemid=123) OR
      (grade=1 AND page IS NULL AND page2 IS NULL AND gemid IS NULL ) OR
      (grade=1 AND page ="page1" AND page2 IS NULL AND gemid IS NULL ) OR
      (grade=1 AND page ="page1" AND page2 = "page2" AND gemid IS NULL )

Answer 1

我能想到的最好办法是计算 WHERE 语句的哪一部分符合条件，并将其作为字段 level 包含在结果集中 - 然后排序用户ID，级别。然后我可以为结果集中的每个用户获取第一条记录。我不知道如何在不创建临时表的情况下只为每个用户包含一条记录。

实际上，这不是任何解决方案，因为我也可以在 php 中处理它。

SELECT gemid, grade, page, page2, subscribeid, userid,
    CASE WHEN (gemid=123) THEN 1 ELSE
      CASE 
      WHEN (grade='1' AND page IS NULL AND page2 IS NULL AND gemid IS NULL ) THEN 2
          ELSE
              CASE 
              WHEN (grade='1' AND page ="page1" AND page2 IS NULL AND gemid IS NULL ) THEN 3 
              ELSE
                  CASE 
                  WHEN (grade='1' AND page ="page1" AND page2 = "page2" AND gemid IS NULL ) THEN 4 
                  ELSE
                      null
                  END
              END
          END
      END level
    FROM subscribe s
    WHERE (gemid=123) OR
          (grade='1' AND page IS NULL AND page2 IS NULL AND gemid IS NULL ) OR
          (grade='1' AND page ="page1" AND page2 IS NULL AND gemid IS NULL ) OR
          (grade='1' AND page ="page1" AND page2 = "page2" AND gemid IS NULL )
    ORDER BY userid, level