SQL Server语言 - Using SQL JOIN and UNION

Question

我有 2 个成功的 SQL 查询，我正在尝试将它们合并为 1 个。 一个查询获取所有产品的总成本，另一个查询获取这些产品的总收入。 我想有第三个按利润(收入 - 成本(排序的查询。

查询 1 使用 UNION ALL 按产品 ID (id( 汇总 3 个类似表中的总成本(支出(。

SELECT id, SUM(spend) as spend_total
FROM (
SELECT id, spend 
FROM vendor_1 
UNION ALL
SELECT id, spend 
FROM vendor_2
UNION ALL
SELECT id, spend 
FROM vendor_3
) as SpendTotal  
GROUP BY id ORDER BY spend_total DESC

查询 2 汇总了 1 个表中的总收入 (rev(

SELECT id, SUM(rev) as rev_total 
FROM income
GROUP BY id ORDER by rev_total DESC

这是我尝试加入两者并按(rev - spend(对其进行排序的失败尝试

SELECT income.id, total_rev - total_spend as result 
FROM (SELECT id, SUM(rev) as total_rev
FROM income
GROUP BY id) as rev
JOIN (SELECT id, SUM(spend) as total_spend
FROM (
SELECT id, spend 
FROM vendor_1 
UNION ALL
SELECT id, spend 
FROM vendor_2
UNION ALL
SELECT id, spend 
FROM vendor_3
) as SpendTotal
GROUP BY id) as vendor_1 ON vendor_1.id = income.id order by result DESC

Answer 1

在SELECT和JOIN中，您使用的是income.id而不是rev.id。

您可以尝试以下查询吗：

SELECT id, result
FROM (
SELECT rev.id, rev.total_rev - vendor_4.total_spend AS result
FROM (
SELECT id, SUM(rev) AS total_rev
FROM income
GROUP BY id
) AS rev
JOIN (
SELECT id, SUM(spend) AS total_spend
FROM (
SELECT id, spend
FROM vendor_1
UNION ALL
SELECT id, spend
FROM vendor_2
UNION ALL
SELECT id, spend
FROM vendor_3
) AS SpendTotal
GROUP BY id
) AS vendor_4 ON vendor_4.id = rev.id
) R
ORDER BY result DESC

Answer 2

你应该再使用一个子查询。 试试这个——

select T1.*,T2.*
FROM
(
SELECT id, SUM(spend) as spend_total
FROM (
SELECT id, spend 
FROM vendor_1 
UNION ALL
SELECT id, spend 
FROM vendor_2
UNION ALL
SELECT id, spend 
FROM vendor_3
) as SpendTotal  
GROUP BY id
) as T1
JOIN
(    
SELECT id, SUM(rev) as rev_total 
FROM income
GROUP BY id 
) as T2
ON T1.Id = T2.Id