如何实现按顺序查询三张表并取首个有数据的查询结果？

完全可以用SQL实现你的需求，不需要依赖编程语言，下面提供两种可行方案：

方案一：优先级标记法（适合三张表字段结构兼容的场景） #

给每个查询结果添加优先级标记，TABLE_A优先级最高（1），TABLE_B次之（2），TABLE_C最低（3）。先找出所有结果中最高的优先级（也就是第一个有数据的表），再返回对应表的结果：

WITH cte_a AS (
    SELECT *, 1 AS priority
    FROM TABLE_A
    WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
),
cte_b AS (
    SELECT *, 2 AS priority
    FROM TABLE_B
    WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
      AND TO_DATE(LAST_MODIFIED_DATE) >= DATEADD(DAY, -7, CURRENT_DATE())
),
cte_c AS (
    SELECT *, 3 AS priority
    FROM TABLE_C
    WHERE MAC IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
),
all_results AS (
    SELECT * FROM cte_a
    UNION ALL
    SELECT * FROM cte_b
    UNION ALL
    SELECT * FROM cte_c
),
top_priority AS (
    SELECT MIN(priority) AS selected_priority FROM all_results
)
SELECT *
FROM all_results
WHERE priority = (SELECT selected_priority FROM top_priority);

方案二：条件分支法（适合三张表字段结构不同的场景） #

通过NOT EXISTS判断高优先级表是否有数据，只有当高优先级表无结果时，才会执行低优先级表的查询：

-- 优先返回TABLE_A的结果（如果有数据）
SELECT *
FROM TABLE_A
WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')

UNION ALL

-- 仅当TABLE_A无数据时，返回TABLE_B的结果
SELECT *
FROM TABLE_B
WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
  AND TO_DATE(LAST_MODIFIED_DATE) >= DATEADD(DAY, -7, CURRENT_DATE())
  AND NOT EXISTS (
      SELECT 1 FROM TABLE_A WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
  )

UNION ALL

-- 仅当TABLE_A和TABLE_B都无数据时，返回TABLE_C的结果
SELECT *
FROM TABLE_C
WHERE MAC IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
  AND NOT EXISTS (
      SELECT 1 FROM TABLE_A WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
  )
  AND NOT EXISTS (
      SELECT 1 FROM TABLE_B WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
        AND TO_DATE(LAST_MODIFIED_DATE) >= DATEADD(DAY, -7, CURRENT_DATE())
  );

补充：编程语言实现思路 #

如果用代码实现逻辑会更直接：按顺序执行三个查询，每执行完一个就检查是否有返回数据。如果有，直接返回该结果并终止后续查询；如果没有，继续执行下一个查询。比如Python中可以通过cursor.fetchall()判断结果是否为空，Java中用ResultSet.next()判断是否有数据。

内容的提问来源于stack exchange，提问作者Abhi