sql - SQL Server SQL查询用 Sum ( ) 连接两个查询

  显示原文与译文双语对照的内容
53 4

我知道这对懂sql人来说很简单,但是, 我想把两个简单的查询合二为一。从代码中可能很明显 job.ActiveJob.ID = job.EstimateTbl.ID_Job 。

我正在尝试获取:id,JobName,sum ( itemAmount )

非常感谢您的帮助.

SELECT a.id, a.JobName
FROM job.ActiveJobsTbl AS a
WHERE a.ID = '100' 
SELECT SUM(itemAmount)
FROM job.EstimateTbl
WHERE ID_Job = '100'
时间:原作者:0个回答

143 4

你可以通过 JOINON E.ID_Job = A.ID并使用 GROUP BY 获取 SELECT 中的值

SELECT A.id, A.JobName, SUM(E.itemAmount) AS Amount 
FROM job.ActiveJobsTbl AS A
INNER JOIN job.EstimateTbl E ON E.ID_Job = A.ID 
WHERE A.ID = '100' 
GROUP BY A.id, A.JobName;
原作者:
62 1

使用 Left join

SELECT a.id, a.JobName,EstimateTbl.itemAmount
FROM job.ActiveJobsTbl AS a
left join
( 
 SELECT SUM(itemAmount) itemAmount,ID_Job
 FROM job.EstimateTbl
 group by ID_Job
) EstimateTbl on EstimateTbl.ID_Job = a.ID
WHERE a.ID = '100' 
原作者:
100 3

你可以使用 APPLY:

select a.id, a.JobName, t.itemAmount 
from job.ActiveJobsTbl AS a OUTER APPLY
 (SELECT SUM(t.itemAmount) AS itemAmount
 FROM job.EstimateTbl AS t
 WHERE t.ID_Job = a.ID
 ) t
WHERE a.ID = 100; 
原作者:
120 2

尝试这个:

SELECT A.id, A.JobName, SUM(E.itemAmount) AS Amount 
FROM job.ActiveJobsTbl AS A
INNER JOIN job.EstimateTbl E ON E.ID_Job = A.ID 
WHERE A.ID = '100'
GROUP BY A.id, A.JobName;
原作者:
...