在单查询中对比两个SELECT查询结果，输出差异标识

以下是整合后的查询语句，可直接返回差异标识：结果不等返回1，相等则返回0。

SELECT
  CASE
    WHEN first_query_result != second_query_result THEN 1
    ELSE 0
  END AS difference_flag
FROM (
  -- 第一个查询的统计结果
  SELECT sum(mass) AS first_query_result
  FROM (
    SELECT SAFE_CAST(SUM(column1) AS INT) AS mass, 'dummy' FROM `dataset.table1` WHERE DATE(dateTime) = CURRENT_DATE()-1
    UNION ALL
    SELECT SAFE_CAST(SUM(anothercol) AS INT) AS mass, 'dummy' FROM `dataset.table2` WHERE DATE(dateTime) = CURRENT_DATE()-1
    UNION ALL
    SELECT SAFE_CAST(SUM(column3) AS INT) AS mass, 'dummy' FROM `dataset.table3` WHERE DATE(dateTime) = CURRENT_DATE()-1
  ) x
) first_q,
(
  -- 第二个查询的统计结果
  SELECT IFNULL(SAFE_CAST(SUM(colmass) AS INT), 0) AS second_query_result
  FROM `dataset1.table4`
  WHERE DATE(timeInterval) = CURRENT_DATE()-1
    AND country IN (
      SELECT ctry FROM `dataset2.table5` 
      WHERE DATE(bqInsertTime) = CURRENT_DATE() 
        AND county='Utopia' 
        AND contactType IN ('call','sms','outlook')
    )
) second_q

逻辑说明 #

• 把两个原查询分别封装为子查询，各自计算出对应的统计值
• 通过隐式交叉连接将两个子查询的结果合并到同一行，方便直接对比
• 用CASE WHEN语句判断两个统计值是否相等，输出对应的差异标识

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