如何在SQL Server中实现多表统计结果的数值相减？

可以用以下几种方式实现三个表COUNT结果的相减运算：

方法一：直接子查询计算 #

最简洁的方式是将三个COUNT查询作为子查询，在主查询中直接执行减法，一次返回最终结果：

SELECT 
    (SELECT COUNT(A.Id) FROM Table_A AS A WITH (NOLOCK) WHERE A.City = 'NewYork')
    - (SELECT COUNT(B.Id) FROM Table_B AS B WITH (NOLOCK) WHERE B.City = 'England')
    - (SELECT COUNT(C.Id) FROM Table_C AS C WITH (NOLOCK) WHERE C.City = 'Berlin') AS Result;

方法二：用CTE分步获取计数再计算 #

如果需要先查看每个表的计数，再做运算，可以用公共表表达式（CTE）先存储各个表的计数结果：

WITH CountValues AS (
    SELECT 'Table_A' AS TableName, COUNT(Id) AS CountNum FROM Table_A WITH (NOLOCK) WHERE City = 'NewYork'
    UNION ALL
    SELECT 'Table_B' AS TableName, COUNT(Id) AS CountNum FROM Table_B WITH (NOLOCK) WHERE City = 'England'
    UNION ALL
    SELECT 'Table_C' AS TableName, COUNT(Id) AS CountNum FROM Table_C WITH (NOLOCK) WHERE City = 'Berlin'
)
SELECT 
    (SELECT CountNum FROM CountValues WHERE TableName = 'Table_A')
    - (SELECT CountNum FROM CountValues WHERE TableName = 'Table_B')
    - (SELECT CountNum FROM CountValues WHERE TableName = 'Table_C') AS Result;

方法三：用变量存储中间值再计算 #

适合需要复用中间计数结果的场景，先把每个表的COUNT值存入变量，再执行减法：

DECLARE @CountA INT, @CountB INT, @CountC INT;

SELECT @CountA = COUNT(Id) FROM Table_A WITH (NOLOCK) WHERE City = 'NewYork';
SELECT @CountB = COUNT(Id) FROM Table_B WITH (NOLOCK) WHERE City = 'England';
SELECT @CountC = COUNT(Id) FROM Table_C WITH (NOLOCK) WHERE City = 'Berlin';

SELECT @CountA - @CountB - @CountC AS Result;

以上三种方式都能得到你需要的9-1-3=5的结果，可根据实际需求选择使用。

内容的提问来源于stack exchange，提问作者Tarık Kantar