ISNULL语法挑战

Question

我似乎找不出这里的语法问题。

这是可行的，但却是空的；

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    (SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

所以我试图在这里使用ISNULL，但是我得到了一个错误：

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    (ISNULL(SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation,0)) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

错误是: Msg 156，第15级，状态1，第2行，在关键字“SELECT”附近不正确的语法。Msg 102，级别15，状态1，第4行语法不正确。

有人能看到我在这里错过了什么吗？

谢谢!

Answer 1

试试这个：

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    ISNULL((SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation),0) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

Answer 2

我不认为你需要一个相关的子查询。在我看来，只需要一个标准的左联接就行了。

SELECT jo.Job_Operation
    , jo.Job
    , jo.Work_Center
    , jo.Operation_Service
    , jo.Est_Total_Hrs
    , SUM(isnull(jot.Act_Run_Hrs, 0)) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
left join PRODUCTION.dbo.Job_Operation_Time jot ON jot.Job_Operation = jo.Job_Operation
WHERE jo.Job = 'A5076027'
GROUP BY
    jo.Job_Operation
    , jo.Job
    , jo.Work_Center
    , jo.Operation_Service
    , jo.Est_Total_Hrs

Answer 3

这在我看来要清楚得多。

SELECT
   jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs
   ,ISNULL(jot.[SumAct_Run_Hrs],0) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
LEFT JOIN (
    SELECT Job_Operation,SUM(Act_Run_Hrs) [SumAct_Run_Hrs]
    FROM PRODUCTION.dbo.Job_Operation_Time
    GROUP BY Job_Operation
) jot ON jot.Job_Operation = jo.Job_Operation
WHERE jo.Job = 'A5076027'