在SQL中获取两个计数的差异

Question

我正在 Netezza 中进行一些 QA，我需要比较两个单独的 SQL 语句的计数。这是我当前正在使用的 SQL

SELECT COUNT(*) AS RECORD_COUNT  
FROM db..EXT_ACXIOM_WUL_FILE  A
LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,mid_key from db..F_EMAIL) B
ON A.MID_KEY=B.MID_KEY
MINUS
SELECT COUNT(*)
FROM db..EXT_ACXIOM_WUL_FILE  A

但是，看起来 MINUS 并不像那样工作。当计数匹配时，Record_count 将返回 null，而不是返回 0。我基本上将记录计数计算为：

record_count=count1-count2

因此，如果计数相等，则为 0，否则为差值。正确的 SQL 是什么？

Answer 1

SELECT
(
SELECT COUNT(*) AS RECORD_COUNT  
FROM db..EXT_ACXIOM_WUL_FILE  A
LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,mid_key from db..F_EMAIL) B
ON A.MID_KEY=B.MID_KEY
)  -
(
SELECT COUNT(*)
FROM db..EXT_ACXIOM_WUL_FILE  A
) TotalCount

Oracle 的 MINUS (EXCEPT< /a> 在 SQL Server 中）是完全不同的动物:)

如果您理解 UNION 然后思考集合，您就会理解 MINUS / EXCEPT

Answer 2

MINUS 是设置差值，不适用于算术运算。

你可以这样做

SELECT COUNT(*) - (SELECT COUNT(*)
                   FROM   db..EXT_ACXIOM_WUL_FILE A) AS Val
FROM   db..EXT_ACXIOM_WUL_FILE A
       LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,
                                  mid_key
                  from   db..F_EMAIL) B
         ON A.MID_KEY = B.MID_KEY  

或其他选择

SELECT COUNT(*) - COUNT(DISTINCT A.PrimaryKey) AS Val
FROM   db..EXT_ACXIOM_WUL_FILE A
       LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,
                                  mid_key
                  from   db..F_EMAIL) B
         ON A.MID_KEY = B.MID_KEY  

Answer 3

我想这可能就是您正在寻找的

    SELECT COUNT(distinct(CURRENTLY_OPTED_IN_FL + F_EMAIL.MID_KEY)) - count(distinct(EXT_ACXIOM_WUL_FILE.MID_KEY))
    FROM EXT_ACXIOM_WUL_FILE 
    LEFT OUTER JOIN F_EMAIL
      ON JOIN F_EMAIL.MID_KEY = EXT_ACXIOM_WUL_FILE.MID_KEY

Answer 4

在 Oracle 数据库上，扩展上面 Adriano Carneiro 的答案，应该可以解决问题：

SELECT (
SELECT COUNT(*) AS RECORD_COUNT  
FROM db..EXT_ACXIOM_WUL_FILE  A
LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,mid_key from db..F_EMAIL) B
ON A.MID_KEY=B.MID_KEY
)
-
(SELECT COUNT(*)
FROM db..EXT_ACXIOM_WUL_FILE  A)
CustomVarName from DUAL;

ps
减号只是数学中集合的标准运算