mysql - SELECT with INNER JOIN 计算增量和过滤结果

Question

我正在尝试从表 B 中选择具有相同 id 的所有行，并计算表 A 中的 count 值和从 B 中选择的行是这样的:

SELECT A.id, 
       A.count, 
       count(*) AS counter, 
       CAST(count(*) AS SIGNED) - CAST(A.count AS SIGNED) AS delta 
FROM table_A AS A 
INNER JOIN table_B AS B ON B.id = A.id 
GROUP BY A.id

现在我已经计算了 delta 但无法过滤 delta > 0 的结果我试过在各处放置 HAVING delta > 0 和 WHERE delta > 0，但没有成功。看起来我在查询中遗漏了一些范围。

Answer 1

尝试使用这个查询

SELECT * FROM (SELECT A.id, 
   A.count, 
   count(1) AS counter, 
   CAST(count(1) AS SIGNED) - CAST(A.count AS SIGNED) AS delta 
FROM table_A AS A 
INNER JOIN table_B AS B ON B.id = A.id 
GROUP BY A.id) as t where delta > 0