SQL 两个sql查询之间的减法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1589070/
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
Subtraction between two sql queries
提问by andreas
I have 2 queries in MS SQL that return a number of results using the COUNT function.
我在 MS SQL 中有 2 个查询，它们使用 COUNT 函数返回许多结果。
I can run the the first query and get the first result and then run the other one to get the other result, subtract them and find the results; however is there a way to combine all 3 functions and get 1 overall result
我可以运行第一个查询并获得第一个结果，然后运行另一个查询以获得另一个结果，减去它们并找到结果；但是有没有办法组合所有 3 个函数并获得 1 个整体结果
As in: run sql1 run sql2 run SQL3 (sql1-sql2)?....
如：运行 sql1 运行 sql2 运行 SQL3 (sql1-sql2)？....
I tried them with xxxx as a function but no luck.
我用 xxxx 作为函数尝试过它们，但没有运气。
回答by Joey
You should be able to use subqueries for that:
您应该能够为此使用子查询：
SELECT
(SELECT COUNT(*) FROM ... WHERE ...)
- (SELECT COUNT(*) FROM ... WHERE ...) AS Difference
Just tested it:
刚刚测试了一下：
Difference
-----------
45
(1 row(s) affected)
回答by Gary McGill
SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)
回答by Sven M.
I know this is an old post but here is another solution that fit best to my needs (tested on firebird)
我知道这是一篇旧帖子，但这是另一个最适合我需求的解决方案（在 firebird 上测试）
SELECT c1-c2 from (select count(*) c1 from t1), (SELECT COUNT(*) c2 from t2);
回答by sourabh bodkhe
This will return the difference
这将返回差异
SELECT COUNT(Attribute) - COUNT(DISTINCT Attribute) FROM table_name;
回答by Ayesha Shaik
The query is like below :
查询如下：
SELECT (select COUNT(FIRSTNAME) FROM TRMDW.EMPLOYEE1) - (SELECT COUNT(DISTINCT FIRSTNAME) FROM TRMDW.EMPLOYEE1) as difference from dual;
回答by Sanjay C
This can be done in a single query:
这可以在单个查询中完成：
SELECT COUNT(col_name) - COUNT(DISTINCT col_name) as Difference from table_name;
回答by Csaxena
SELECT (count(*) from t1) - (count(*) from t2);
this worked for me.
这对我有用。
Also if there is only one table you can also do:
此外，如果只有一张桌子，您也可以这样做：
SELECT (count(column1)) - count(column2)) from table;
回答by Philip Kelley
SELECT
t1.HowManyInTable1
,t2.HowManyInTable2
,t1.HowManyInTable1 = t2.HowManyInTable2 Table1_minus_Table2
from (select count(*) HowManyInTable1 from Table1) t1
cross join (select count(*) HowManyInTable2 from Table2) t2
回答by John Lechowicz
Just create an inline function with your query logic, and have it return the result. Pass in parameters as needed.
只需使用您的查询逻辑创建一个内联函数，并让它返回结果。根据需要传入参数。
回答by Justin Niessner
select @result = (select count(0) from table1) - (select count(0) from table2)
