如何在两个表之间执行数学运算

debugcn 发表于 Dev

马修浸信会

我正在运行两个 SQL 查询，它们返回给我一个每个有 3 行的表，我需要执行一个数学运算符来从两个表中减去每一行。

这是第一个查询，它返回 3 行 x 值，如

SELECT COUNT (*) AS MES 
FROM WorkOrder
WHERE asset LIKE '%DC1%'
AND YEAR (workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01'

UNION ALL

SELECT COUNT (*) AS MES 
FROM WorkOrder
WHERE asset LIKE '%DC2%'
AND YEAR (workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01'

UNION ALL

SELECT COUNT (*) AS MES 
FROM WorkOrder
WHERE asset NOT LIKE '%DC1%' AND asset NOT LIKE '%DC2%'
AND YEAR (workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01

这是第二个查询，它返回与其他值相同的内容

SELECT COUNT(*) AS MES
FROM WorkOrder
WHERE asset LIKE '%DC1%' 
AND YEAR(workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01'
AND businessUnit ='MM'

UNION ALL

SELECT COUNT(*) AS MES
FROM WorkOrder
WHERE asset  LIKE '%DC2%' 
AND YEAR(workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01'
AND businessUnit ='MM'

UNION ALL

SELECT COUNT(*) AS MES
FROM WorkOrder
WHERE asset NOT LIKE '%DC1%' AND asset NOT LIKE '%DC2%'
AND YEAR(workOrderDate) BETWEEN 2018/11/01 AND 2018/11/31
OR businessUnit ='MM'
OR workType = '07'
OR workType = '08'
OR workType = '09'
OR workType = '10'
OR workType = '01'
AND businessUnit ='MM'

我需要执行一个操作来从每个查询中减去值，比如

SQL 中有没有一种方法可以做到这一点？我只能通过同一张表找到运算符，无法弄清楚如何执行此操作。

豪尔赫·洛佩兹

像这样为每个表提供一个 row_number 。

select t1.*, t2.b, t1.mes - t2.mes from
(select *,row_number() over (order by mes) a from table1)t1 join
(select *,row_number() over (order by mes) b from table2)t2 on t1.a = t2.b

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。