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'



时间:

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;



原作者:
...