oracle SQL*Plus：两个表中行数的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4710919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL*Plus: Difference in number of rows in two tables
提问by Moeb
How can I take a difference in the counts of the number of rows in two different tables?
如何区分两个不同表中的行数？
SQL> select count(*) from dual44;
COUNT(*)
----------
3
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> (select count(*) from dual44)
2 minus
3 (select count(*) from dual)
4 ;
COUNT(*)
----------
3
SQL>
I need 2as the result. The two tables might not necessarily have the same scehma.
2结果我需要。这两个表可能不一定具有相同的架构。
回答by lweller
minus operator is used to removed all records of first result set that are also contained in second. Use - (dash) operator here.
减号运算符用于删除第一个结果集中也包含在第二个结果集中的所有记录。在此处使用 -（破折号）运算符。
select ((select count(*) from dual44) - (select count(*) from dual)) from dual
回答by nan
回答by PietroSv
SELECT (a.count-b.count)
FROM
(SELECT COUNT(*) count FROM dual44) a,
(SELECT COUNT(*) count FROM dual) b;
回答by bonsvr
The following is really fast( less than 1 second for tables of hundreds of millions records) provided that your statistics are up to date (which is highly recommended).
如果您的统计数据是最新的（强烈推荐），以下操作非常快（对于包含数亿条记录的表，不到 1 秒）。
select
(
(select u.NUM_ROWS from user_tables u where u.TABLE_NAME='JOBS')
-
(select u.NUM_ROWS from user_tables u where u.TABLE_NAME='COUNTRIES')
) DIFF
from dual
DIFF
----------
-6
Sample code applies to HR schema of ORACLE. You can change the table names.
示例代码适用于 ORACLE 的 HR 模式。您可以更改表名。
The two tables might not necessarily have the same schema.
这两个表可能不一定具有相同的架构。
Edit on Jeffrey Kemp's comment:
编辑 Jeffrey Kemp 的评论：
If tables are on different schemas, you have to use dba_tables, if you have the privileges.
如果表位于不同的架构上，则必须使用dba_tables, 如果您有权限。
Just add the schema name to the table name. i.e. HR.JOBS
只需将模式名称添加到表名称。IEHR.JOBS
回答by enaJ
In Sqlite, this would works:
在Sqlite 中，这会起作用：
Select (Select count(col1) from Table1) - (Select count(col2) from Table2))
