首页 \ 问答 \ 计算其他表中不存在的所有记录 - SQL查询(Count all records that does not exist to other table - SQL Query)

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

我有两(2)个表,我试图计算Table1中的所有记录,Table1_delta是Table1_delta中的pagename尚未列入Table1。 Table1_delta中的Incase页面名称列在Table1中,status必须为1,以便它包含在计数结果中。

样本表结构:

表格1

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

Table1_delta

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

表格样本应返回“3”

tablename3和pagename4未在Table1中列出(返回2),Table1中的pagename2的status = 1(返回1)。 总共有3个来自Table1_delta的页面名称未在表1中列出,并且从Table1记录,其中status = 1.我想知道如何查询这个? 我正在使用MySQL v5.6.17。 谢谢!


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.

Sample table structure:

Table1

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

Table1_delta

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

The table sample should return "3".

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!


原文:https://stackoverflow.com/questions/32687899
更新时间:2019-07-13 09:05

最满意答案

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

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

以下是上述查询中的临时表:

+-----------+--------+
| 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
+-----------+--------+

查看下面的链接,了解正在运行的演示。

SQLFiddle


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

2015-09-21

相关问答

更多

如何从其他表中不存在的表中选择所有记录?(How to select all records from one table that do not exist in another table?)

SELECT t1.name FROM table1 t1 LEFT JOIN table2 t2 ON t2.name = t1.name WHERE t2.name IS NULL 问 :这里发生了什么? A :从概念上讲,我们从table1选择所有的行,并且我们尝试在table2找到与name列相同的值的每一行。 如果没有这样的行,我们只需将该结果的table2部分留空。 然后我们通过仅选择匹配行不存在的结果中的那些行约束我们的选择。 最后,我们忽略我们结果中的所有字段,除了name列(我 ...

记录与SQL中其他表的所有记录有关(Record that is related to all records of other table in SQL)

我注意到我是唯一使用Person_overcomes_obstacle的自然表别名的Person_overcomes_obstacle ! 您需要为此进行关系部门查询。 您可以计算Person_overcomes_obstacle中的障碍物和匹配记录,并返回2个数字相匹配的记录,或者以另一种方式查找没有障碍但没有克服的人。 SELECT p.id, p.name /*Or use COUNT(*) or wrap in Exists if you don ...

sql查找子查询中不存在的数据(sql Find data that does NOT exist in a subquery)

看起来你非常接近,子查询可能甚至不是必需的,但这种修改应该能得到正确的结果。 但是如上所述,您需要一种方法来引用外部查询,因此需要dbo.Registry r别名。 select distinct wbs1, wbs2, wbs3 from dbo.Registry r where not exists ( select 1 from Registry where ISNULL(In ...

SQL Server:检查所有行是否存在于其他表中(SQL Server : check if all rows exists in other table)

IF NOT EXISTS ( SELECT ID FROM @Table1 EXCEPT SELECT ID FROM @Table2 ) SELECT 'yes exists' ELSE SELECT 'no, doesn''t exist' IF NOT EXISTS ( SELECT ID FROM @Table1 EXCEPT SELECT ID FROM @Table2 ) SELECT 'yes exists' ELSE SELECT ...

从表中删除另一个表中不存在的所有记录(Removing all records from a table that don't exist in another table)

DELETE FROM old_table WHERE id NOT IN (SELECT id FROM temp_table) 编辑: id NOT IN (SELECT id FROM temp_table)是一个糟糕的构造。 如果你有大表,并且你的服务器设置被调整到低内存,你将永远执行它。 @Piskvor的答案是一个更长的,但更好的查询,这将在低内存设置上效果最好。 当我说“低内存”时,我的意思是数据库服务器不占用全部高端计算机,几乎没有任何系统管理小型企业网站或类似的东西。 DELE ...

SQL查询查找count> 1的记录(SQL query for finding records where count > 1)

使用HAVING子句和GROUP由使行唯一的字段 下面会发现 所有拥有相同帐号的用户每天都有多笔付款 SELECT user_id , COUNT(*) count FROM PAYMENT GROUP BY account, user_id , date Having COUNT(*) > 1 更新如果您只想包括那些具有不同的ZIP,您可以先获得一个不同的集合,然后执行你HAVING / GROUP BY SELECT user_id, account_no ...

如何使用SQL Server中的关联表计算查询中的记录?(How to Count records in query with association table in SQL Server?)

不太清楚你想要得到什么,但有两个选项要检查。 使用COUNT(Distinct ItemsToP.ItemID)而不是COUNT(ItemsToP.ItemID),您可能在一个包中包含相同的项目两次(具有不同的数量),并且col'NumberOfDifferentItems'的命名也建议使用distinct。 但是,您的问题是“每个PackingList的商品数量”。 根据我的理解,你应该总结数量,SUM(ItemsToP.Quantity)而不是计算ID。 Not really clear wh ...

SQL查询查找表中不存在的日期(SQL Query finding Dates which are not Present in a Table)

你需要一个日历表来做到这一点。 我使用Recursive CTE来生成日期。 但我会建议在你的数据库中创建一个日历表,并在像这样的查询中使用它 DECLARE @startdate DATE = '2018-01-01', @enddate DATE = '2018-01-31'; WITH calendar AS (SELECT @startdate AS Dates UNION ALL SELECT Dateadd(dd, 1 ...

相关文章

更多

最新问答

更多
  • Unity着色器错误;(Unity shader error; presumably in if statement)
  • 如何在Liferay portlet中设置Cookie?(How to set a Cookie in Liferay portlet?)
  • C#只读访问List <>或其他集合,或ToArray()魔术(C# readonly access to List<> or other collection, or ToArray() magic)
  • 如何使用Google Web工具包创建登录应用程序?(How to create a login application using Google web tool kit? [closed])
  • 如何使我的函数返回结果作为全局变量?(How to make my function return results as a global variable?)
  • 为什么我的网站需要“启用32位应用程序”?(Why does my website need “Enable 32-bit applications”?)
  • 红宝石数组具有相同的值(ruby array of hash with same value)
  • Android(在Scala中):StackOverflowError取决于何时启动线程?(Android (in Scala): StackOverflowError depends on when to start a thread?)
  • 适用于iOS应用的通用链接(Universal Links for iOS apps)
  • 创建并打开文件linux编程(create and open file linux programming)
  • Datepicker,第2个日期是从第1个日期开始的X天(Datepicker, 2nd date is X days from 1st date)
  • 背景大小过渡不起作用(Transition on background-size doesn't work)
  • 在React / Redux中的On Click函数中传递Prop(Passing a Prop in an On Click Function in React/Redux)
  • 关键字在代码中做了什么,是否有没有此关键字的替代方法?(what does the keyword this does in the code and are there any alternate methods without this keyword? [duplicate])
  • 反向设计FoxPro / dBsae数据库以创建EER模型(Reverse Engineer a FoxPro/dBsae database to create the EER model)
  • 在R中的变量中组合具有相同值的行(Combine rows that have same value in a variable in R [duplicate])
  • SQL Server:非空唯一主键(SQL Server: Non-null unique vs. Primary Key)
  • 将扩展ASCII字符代码转换为ISO-8859-1(Convert extended ASCII character codes to ISO-8859-1)
  • 如何在.NET中读取m4a文件中的标签?(How to read tags out of m4a files in .NET?)
  • 呼叫链接的性能提升?(Performance gain on call chaining?)
  • 风景名胜区规划设计文本里面都包含哪些内容?推荐一家旅游规划设计公司?谢谢!
  • 如何在String中将字符串转换为枚举?(How do I convert a string to enum in TypeScript?)
  • SQLite SELECT出现异常如何解决它?(SQLite SELECT gives exception How to fix it?)
  • NSLog不会从ViewController类输出,而是来自AppDelegate类的WILL。(NSLog will NOT output from the ViewController class, but WILL from the AppDelegate class. iOS:Objective C)
  • 更改UIPicker突出显示的标签宽度(change UIPicker highlighted tab width)
  • 空心倒五角形(Hollow inverted pentagon)
  • 如何设置仅使用OpenID的Plone站点(How to setup Plone sites working only with OpenID)
  • Paperclip缺少Amazon S3的协议(https)(Paperclip is missing the Protocol (https) with Amazon S3)
  • 从宏中部署netbeans中的ANT文件(Deploy ANT file in netbeans from macro)
  • XMLReader是未知的(XMLReader is unknown)