当前位置：文江博客话题详情

两个sql查询之间的减法

发布于 2024-08-08 00:34:07 字数 220 浏览 10 评论 0原文

我在 MS SQL 中有 2 个查询，它们使用 COUNT 函数返回多个结果。

我可以运行第一个查询并获取第一个结果，然后运行另一个查询以获得另一个结果，将它们相减并找到结果；但是有没有办法结合所有 3 个函数并获得 1 个总体结果

，如： run sql1 run sql2 run SQL3 (sql1-sql2)？....

我用 xxxx 作为函数尝试了它们，但没有运气。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暖树树初阳… 2024-08-15 00:34:07

您应该能够为此使用子查询：

SELECT
    (SELECT COUNT(*) FROM ... WHERE ...)
  - (SELECT COUNT(*) FROM ... WHERE ...) AS Difference

刚刚测试过：

Difference
-----------
45

(1 row(s) affected)

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)

回复收藏 0 原文

养猫人 2024-08-15 00:34:07

SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)

SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)

回复收藏 0 原文

傲娇萝莉攻 2024-08-15 00:34:07

这将返回差值

SELECT COUNT(Attribute) - COUNT(DISTINCT Attribute) FROM table_name;

This will return the difference

SELECT COUNT(Attribute) - COUNT(DISTINCT Attribute) FROM table_name;

回复收藏 0 原文

凉栀 2024-08-15 00:34:07

我知道这是一篇旧文章，但这是另一个最适合我的需求的解决方案（在 firebird 上测试）

SELECT c1-c2 from (select count(*) c1 from t1), (SELECT COUNT(*) c2 from t2);

I know this is an old post but here is another solution that fit best to my needs (tested on firebird)

SELECT c1-c2 from (select count(*) c1 from t1), (SELECT COUNT(*) c2 from t2);

回复收藏 0 原文

跨年 2024-08-15 00:34:07

查询如下：

SELECT (select COUNT(FIRSTNAME) FROM TRMDW.EMPLOYEE1) - (SELECT COUNT(DISTINCT FIRSTNAME) FROM TRMDW.EMPLOYEE1) as difference from dual;

The query is like below :

SELECT (select COUNT(FIRSTNAME) FROM TRMDW.EMPLOYEE1) - (SELECT COUNT(DISTINCT FIRSTNAME) FROM TRMDW.EMPLOYEE1) as difference from dual;

回复收藏 0 原文

醉城メ夜风 2024-08-15 00:34:07

这可以在单个查询中完成：

SELECT COUNT(col_name) - COUNT(DISTINCT col_name) as Difference from table_name;

This can be done in a single query:

SELECT COUNT(col_name) - COUNT(DISTINCT col_name) as Difference from table_name;

回复收藏 0 原文

榆西 2024-08-15 00:34:07

只需使用查询逻辑创建一个内联函数，并让它返回结果。根据需要传入参数。

回复收藏 0 原文

提赋 2024-08-15 00:34:07

select @result = (select count(0) from table1) - (select count(0) from table2)

select @result = (select count(0) from table1) - (select count(0) from table2)

回复收藏 0 原文

佞臣 2024-08-15 00:34:07

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

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

回复收藏 0 原文

不必你懂 2024-08-15 00:34:07

SELECT (count(*) from t1) - (count(*) from t2);

这对我有用。

另外，如果只有一张表，你也可以这样做：

SELECT (count(column1)) - count(column2)) from table;

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;

回复收藏 0 原文

猥琐帝 2024-08-15 00:34:07

查询如下：

((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(m,'/',2),'/',-1) 
FROM ms WHERE ms.id=t.m_id)-(SELECT COUNT(id) FROM t AS tr WHERE tr.m_id=t.m_id)) AS remaining

The query is like below :

((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(m,'/',2),'/',-1) 
FROM ms WHERE ms.id=t.m_id)-(SELECT COUNT(id) FROM t AS tr WHERE tr.m_id=t.m_id)) AS remaining

回复收藏 0 原文

~没有更多了~