sql-server - 子查询不返回值

Question

我有一个包含子查询的查询，该查询使用子查询的 WHERE 语句中的临时表的结果。但是，这并没有返回我期望的结果。本质上，有一个表 EXT_Workpackage，其中包含 EXT_Workpackage_ID（主键）和 Parent_Workpackage_ID 的值。我正在使用 Parent_Workpackage_ID 返回父级的 Workpackage_Code（工作包可以有多个与之关联的工作包）。

我将复制以下代码：

    USE DeltekPIMQA

    select
    pa.EXT_Workpackage_ID,
    pa.Parent_Workpackage_ID [Parent_Workpackage_ID],
    SUM(CONVERT(DECIMAL(18,2),CONVERT(decimal(18,2),ISNULL(h.Hours,0))/60)) [hrs]

    INTO #tempactualhoursb
    FROM 
    EXT_Workpackage wp
    LEFT JOIN exvw_tmshours h on wp.Workpackage_Code=h.wp collate SQL_Latin1_General_CP1_CI_AS
    LEFT JOIN EXT_Workpackage pa on wp.Parent_Workpackage_ID=pa.EXT_Workpackage_ID
    WHERE wp.Project_ID=868
    and pa.Workpackage_Code is not null
    Group by pa.EXT_Workpackage_ID,pa.Workpackage_Code, pa.Parent_Workpackage_ID
    ORDER BY pa.Workpackage_Code


    select
    wp.EXT_Workpackage_ID,
    wp.Workpackage_Code [Code],
    RIGHT(wp.Workpackage_Code,3) [job],
    wp.name [Name],
    wp.Parent_Workpackage_ID [Parent_Workpackage_ID],
    wps.name [Status],
    wps.name [@stato],
   wpc.name [ContractType],
    wpt.name [LeadTrade],
    --PWC.Workpackage_Code,


    CASE
        WHEN WPS.ext_workpackage_status_id not in (3,13) THEN ISNULL(wp.Duration,0)
        ELSE 0
    END
    [SalesBudget],

    --ISNULL(wp.Duration,0) [SalesBudget],





    ISNULL(tah.hrs,0)- ISNULL(wp.Duration,0) [PerformanceVsSales],
    CAST(ISNULL(CONVERT(INT,(tah.hrs- wp.Duration)/NULLIF(wp.duration,0)*100),0) as varchar(16)) + '%' [Percent],
    (SELECT ISNULL(sum(ch.duration),0) from EXT_Workpackage ch where ch.Parent_Workpackage_ID=wp.ext_workpackage_id and ch.ext_workpackage_status_id not in (3,13)) [PWP_Planned],

    ISNULL(tah.hrs,0) [ActualHours],
    (select CONVERT(varchar,count(*)) from EXT_Workpackage wx where wx.Parent_Workpackage_ID=wp.ext_workpackage_id and wx.EXT_Workpackage_Status_ID in (2)) [WPLive],
    (select CONVERT(varchar,count(*)) from EXT_Workpackage wx where wx.Parent_Workpackage_ID=wp.ext_workpackage_id and wx.EXT_Workpackage_Status_ID in (1,2,5,7,8,12)) + ' / ' +
    (select CONVERT(varchar,count(*)) from EXT_Workpackage wx where wx.Parent_Workpackage_ID=wp.ext_workpackage_id and wx.EXT_Workpackage_Status_ID in (2)) [WPsCountLive],

    (SELECT ISNULL(sum(ch.Material_Cost),0) from EXT_Workpackage ch where ch.Parent_Workpackage_ID=wp.ext_workpackage_id) [PlannedChildMaterials],
    ISNULL(wp.Material_Cost,0) [PlannedMaterials],
    (SELECT TOP 1 Workpackage_Code FROM EXT_Workpackage WHERE Parent_Workpackage_ID = tah.Parent_Workpackage_ID AND Parent_Workpackage_ID IS NOT NULL) [Parent_Workpackage_Code],




    CASE
        WHEN wps.completed=1 THEN 1
        WHEN wps.completed = 0 THEN 0
    END [workpackage/@iscomplete]
    --(SELECT TOP 1 Workpackage_Code FROM EXT_Workpackage WHERE EXT_Workpackage_ID = wp.Parent_Workpackage_ID) AS [Parent_Workpackage_Code]
    from EXT_Workpackage AS wp
    LEFT JOIN Project p on wp.Project_ID=p.Project_ID
    LEFT JOIN EXT_Workpackage_Status wps on wp.EXT_Workpackage_Status_ID=wps.EXT_Workpackage_Status_ID
    LEFT JOIN EXT_Workpackage_Contract_Type wpc on wp.EXT_Workpackage_Contract_Type_ID=wpc.EXT_Workpackage_Contract_Type_ID
    LEFT JOIN EXT_Workpackage_Type wpt on wp.EXT_Workpackage_Type_ID=wpt.EXT_Workpackage_Type_ID
    LEFT JOIN #tempactualhoursb tah on wp.EXT_Workpackage_ID=tah.ext_workpackage_id
    LEFT JOIN EXT_Workpackage_Billing_Type b on wp.Billing=b.EXT_Workpackage_Billing_Type_ID
    --CROSS APPLY   [dbo].[EXT_FN_Parent_Workpackage_Code] (#tempactualhoursb.Parent_Workpackage_ID) PWC ON wp.EXT_Workpackage_ID = PWC.EXT_Workpackage_ID 
    where wp.Project_ID=868

    --AND Parent_Workpackage_Code IS NOT NULL

    order by wp.Workpackage_Code 

标签： sql-servertsql