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

问：

我正在做级联 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）条件的案例。

谢谢！

MySQL 数据库

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
  )

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

上一个：TCPDF：自动分页后图像与 html 表格标题重叠

下一个：OpenGL的 |移动相机后模型不必要的晃动

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

Merge multiple cascaded selects, with priority

评论