MySQL 根据 IF/CASE 语句选择某些行

Question

像下面这样的事情是我想要做的

SELECT id, status, Gid, Uid, note FROM notes LIMIT 10

但状态 id 可能不同，它需要像下面这样

SELECT a.id, a.status, a.Gid, a.Uid, a.note
FROM notes as a
WHERE
  IF(a.status < 2, DONT SELECT),
  IF(a.status == 2, 
    (SELECT approved FROM friends WHERE Uid = a.Uid AND Fid = ".$_SESSION['Uid'].") 
      IF(approved = 1, SELECT ROW, else DONT)
  )
  IF(a.status == 3, SELECT)
  IF(a.status == 4,
     (SELECT Gid FROM friends WHERE Uid = a.Uid AND Fid = ".$_SESSION['Uid'].")
     IF(Gid == a.Gid, SELECT ROW, else DONT)
ORDER BY id DESC
LIMIT 10

我查找了 CASE 命令，但不确定如何使用它，但我知道这是执行此操作的方法，但是是的..我不确定如何使用。上面是我正在尝试做的一个例子，a.Uid 可以是不同的 id，所以当 status == 2 或 4 我们需要查看查看用户是批准的 friend 还是在 groupId (Gid )

任何解决此查询的帮助将不胜感激。如果需要，我可以稍微更改注释表，因为它尚未投入生产，但不鼓励。不过我愿意接受建议

提前致谢!

Answer 1

我想这会做你想做的:

select a.id, a.status, a.Gid, A.Uid, a.note
FROM notes a
LEFT JOIN friends f ON f.Uid = a.Uid AND f.Fid = $_SESSION[Uid]
WHERE (a.status = 2 AND f.approved = 1)
OR a.status = 3
OR (a.status = 4 AND f.Gid = a.Gid)
ORDER BY a.id DESC
LIMIT 10