所以我有一个运行良好的查询，但是我需要在其他一些地方使用工厂std cost，所以我没有多次调用这个函数，而是将它放在一个外部apply中。然而，它现在似乎不起作用。我得到以下错误：
在包含外部引用的聚合表达式中指定了多列。如果要聚合的表达式包含外部引用，则该外部引用必须是表达式中引用的唯一列。
以下是工作查询：

SELECT
    DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
    p.[PART_X] AS [Assembly Part #],
    p.[DESCR_X] AS [Assembly Part Description],
    oa2.[Part Count],
    oa2.[Total # of Parts],
    ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) AS [Factory Std Cost],
    oa2.[# of Docs],
    oa2.[# of Software],
    'Logic Pending' AS [# of Std Cost Items],
    oa2.[# of HR Devices],
    oa2.[# of 3rd Party Devices],
    oa2.[# of Robots],
    oa2.[# of Buy Parts],
    oa2.[# of Make Parts]

  FROM AllPartsList ap
    LEFT JOIN visuser.EN_PART p
      ON p.[EN_Part_ID] = ap.[SourcePartID]
    OUTER APPLY (
        SELECT
            [Part Count]                = COUNT(    DISTINCT IIF( [Qty] = 0, null, [Component Part #])  ),  
            [Total # of Parts]          = SUM([Qty]),
            [# of Docs]                 = COUNT(    DISTINCT IIF( [Commodity Code] IN ('009', '072', '073', '075', '079', '082'), [Component Part #], null) ),  -- Commodity Codes: 009, 072, 073, 075, 079, 082  :  Commodity ID: 15, 84, 85, 87, 81, 92
            [# of Software]             = COUNT(    DISTINCT IIF( [Commodity Code] IN ('034'), [Component Part #], null)    ),                                  -- Commodity Code 034  :  Commodity ID: 28
            [# of HR Devices]           = COUNT(    DISTINCT IIF( [Commodity Code] IN ('002'), [Component Part #], null)    ),                                  -- Commodity Code 002  :  Commodity ID: 11
            [# of 3rd Party Devices]    = COUNT(    DISTINCT IIF( [Commodity Code] IN ('007'), [Component Part #], null)    ),                                  -- Commodity Code 007  :  Commodity ID: 5
            [# of Robots]               = COUNT(    DISTINCT IIF( ( [Commodity Code] IN ('005') /* AND [Make/Buy] = 'B' */ ), [Component Part #], null) )   ,       -- Commodity Code 005  :  Commodity ID: 13
            [# of Make Parts]           = COUNT(    DISTINCT IIF( [Make/Buy] = 'M', [Component Part #], null)   ),
            [# of Buy Parts]            = COUNT(    DISTINCT IIF( [Make/Buy] = 'B', [Component Part #], null)   ),
            [# of Ref Parts]            = COUNT(    DISTINCT IIF( [Make/Buy] = 'B', [Component Part #], null)   )

          FROM bomBreakdown
          WHERE
            [ComponentPartID] IS NOT NULL AND 
            [SourcePartID] = ap.[SourcePartID]
          GROUP BY [SourcePartID]
    ) oa2
    ORDER BY [PART_X]

这是我改的。我将对函数的调用移到了一个outer apply，并在主查询和第二个outer apply中使用了它。错误引用第二个外部应用的第一行 oa1.[Factory Std Cost] ```
SELECT
DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
p.[PART_X] AS [Assembly Part #],
p.[DESCR_X] AS [Assembly Part Description],
oa2.[Part Count],
oa2.[Total # of Parts],
oa1.[Factory Std Cost],
oa2.[# of Docs],
oa2.[# of Software],
'Logic Pending' AS [# of Std Cost Items],
oa2.[# of HR Devices],
oa2.[# of 3rd Party Devices],
oa2.[# of Robots],
oa2.[# of Buy Parts],
oa2.[# of Make Parts]

FROM AllPartsList ap
LEFT JOIN visuser.EN_PART p
ON p.[EN_Part_ID] = ap.[SourcePartID]
OUTER APPLY (
SELECT ([dbo].fn_getFactoryStdCost) AS [Factory Std Cost]
) oa1
OUTER APPLY (
SELECT
[Part Count] = COUNT( DISTINCT IIF( [Qty] = 0, null, [Component Part #]) ),
[Total # of Parts] = SUM([Qty]),
[# of Docs] = COUNT( DISTINCT IIF( [Commodity Code] IN ('009', '072', '073', '075', '079', '082'), [Component Part #], null) ), -- Commodity Codes: 009, 072, 073, 075, 079, 082 : Commodity ID: 15, 84, 85, 87, 81, 92
[# of Software] = COUNT( DISTINCT IIF( [Commodity Code] IN ('034'), [Component Part #], null) ), -- Commodity Code 034 : Commodity ID: 28
[# of HR Devices] = COUNT( DISTINCT IIF( ( [Commodity Code] IN ('002') AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null) ), -- Commodity Code 002 : Commodity ID: 11
[# of 3rd Party Devices] = COUNT( DISTINCT IIF( [Commodity Code] IN ('007'), [Component Part #], null) ), -- Commodity Code 007 : Commodity ID: 5
[# of Robots] = COUNT( DISTINCT IIF( ( [Commodity Code] IN ('005') /* AND [Make/Buy] = 'B' */ ), [Component Part #], null) ) , -- Commodity Code 005 : Commodity ID: 13
[# of Make Parts] = COUNT( DISTINCT IIF( ( [Make/Buy] = 'M' AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null) ),
[# of Buy Parts] = COUNT( DISTINCT IIF( ( [Make/Buy] = 'B' AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null) ),
[# of Ref Parts] = COUNT( DISTINCT IIF( ( [Make/Buy] = 'B' AND oa1.[Factory Std Cost] = 0 ), [Component Part #], null) )

      FROM bomBreakdown
      WHERE
        [ComponentPartID] IS NOT NULL AND 
        [SourcePartID] = ap.[SourcePartID]
      GROUP BY [SourcePartID]
) oa2
ORDER BY [PART_X]

以下是allpartslist的外观：
![](https://i.stack.imgur.com/jkYGH.png)
以及爆炸事件：
![](https://i.stack.imgur.com/DQdBj.jpg)
我还原了查询，然后尝试一次添加一点内容，以查看到底哪里出错了。我加了一行 `[# of Ref Parts]` 只有make/but='b'（基本上与购买部件相同）。在我为stdcost添加函数之前，它工作得很好。然后我收到了同样的外部参考错误。这是一句话：

COUNT( DISTINCT IIF( ( [Make/Buy] = 'B' AND ([dbo].fn_getFactoryStdCost) = 0 ), [Component Part #], null) )

经过一番周旋，我发现问题是 `ap.[SourcePartID]` 在对函数的调用中。这是链接allpartslist和bombreakdown表的关键字段，因此我可以删除 `ap.` 只使用bombreakdown表中的一个，但它并没有解决我最初试图解决的问题，即不多次调用函数。
我正在使用：
sql server 2019（v15.0.2070.41）
ssms版本18.5