mysql - 合并多个级联选择，优先级

Question

我正在执行级联 SELECT:

SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 3
=> If nothing found, try: SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 0
=> If nothing found, try: SELECT * FROM x WHERE a = 1 AND b = 0 AND c = 0

其中至少有一个有结果，但所有情况都可以有结果。我想合并此逻辑并仅返回一个案例，即给定 (!=0) 条件最多的案例。

谢谢!

Answer 1

我可以使用 UNION 查询而不是 EXISTS 来回答:

SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 3

UNION ALL

SELECT * FROM x
WHERE
  a = 1 AND b = 2 AND c = 0
  AND NOT EXISTS (SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 3)

UNION ALL

SELECT * FROM x
WHERE a = 1 AND b = 0 AND c = 0
  AND NOT EXISTS (SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 3)
  AND NOT EXISTS (SELECT * FROM x WHERE a = 1 AND b = 2 AND c = 0)

但更聪明的解决方案是这样的:

SELECT * FROM x
WHERE
  (a, b, c) = (
      SELECT a, b, c
      FROM x WHERE (a, b, c) IN ( (1,2,3), (1,2,0), (1,0,0) )
      ORDER BY (b=0) + (c=0)
      LIMIT 1
  )

我不确定最后一个会有更好的表现。我会试试哪个更好。