两个表的不同计数-使用外部联接吗？

Question

我正试图在Z_KSS和Z_KSS2中查找计数(distinct )。如果MEM_ID同时出现在Z_KSS和Z_KSS2中，则只应计算一次。我用外接吗？我是否需要指定一个On条件？

Select count (distinct MEM_ID), sum(Amount)
      , 'KSS' Company
      FROM    temp_09.Z_KSS
       FULL OUTER JOIN temp_09.Z_KSS2

Answer 1

我猜你想要union all：

SELECT count(distinct MEM_ID), sum(Amount), 'KSS' Company
FROM  ((SELECT MEM_ID, Amount
        FROM temp_09.Z_KSS
       ) UNION ALL
       (SELECT DISTINCT MEM_ID, Amount
        FROM temp_09.Z_KSS2
       )
      ) t;