计算其他表中不存在的所有记录-SQL查询 [英] Count all records that does not exist to other table - SQL Query

问题描述

我有两个(2)表，并且我试图对Table1和Table1_delta中的所有记录进行计数，因为Table1_delta中的页名尚未在Table1中列出.如果Table1_delta中的页面名称列在Table1中，则状态必须为1，这样它才能包含在计数结果中.

I have two(2) tables and I'm trying to count all records from Table1 and Table1_delta were pagename from Table1_delta is not yet listed into Table1. Incase pagename from Table1_delta is listed to Table1, status must be 1 so that it will be included in count result.

示例表结构:

表1

+-----------+--------+
| pagename  | status |
+-----------+--------+
| pagename1 |   2    |
| pagename2 |   1    |
+-----------+--------+

Table1_delta

+-----------+
| pagename  |
+-----------+
| pagename1 |
| pagename2 |
| pagename3 |
| pagename4 |
+-----------+

表示例应返回"3" .

pagename3和pagename4未在Table1中列出(返回2)，而Table1中的pagename2的状态为1(返回1).总的来说，Table1_delta中有 3个页面名称，这些名称没有在Table1中列出，而在Table1中记录，状态为1.我想知道如何查询它?我正在使用MySQL v5.6.17.谢谢！

pagename3 and pagename4 is not listed in Table1(that returns 2) and pagename2 from Table1 has an status = 1(that returns 1). In total there are 3 pagenames from Table1_delta that are not listed in Table1 and record from Table1 where status = 1. I'm wondering on how will be the query of this? I'm using MySQL v5.6.17. Thanks!

推荐答案

以下是使用联接的替代解决方案:

Here is an alternative solution using joins:

SELECT COUNT(*)
FROM Table1_delta t1 LEFT JOIN Table1 t2
ON t1.pagename = t2.pagename
WHERE t2.status IS NULL OR t2.status = 1

这是上面查询中的临时表的样子:

Here is what the temporary table from the above query looks like:

+-----------+--------+
| pagename  | status |
+-----------+--------+
| pagename1 |  2     |    # this row is NOT counted
| pagename2 |  1     |    # +1 this row has status = 1 and is counted
| pagename3 |  null  |    # +1 this row has status = null and is counted
| pagename4 |  null  |    # +1 this row is also null and is counted
+-----------+--------+

查看下面的链接以获取正在运行的演示.

Check out the link below for a running demo.

SQLFiddle

SQLFiddle

这篇关于计算其他表中不存在的所有记录-SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！