SQl错误:每个组按表达式必须包含至少一个不是外部引用的列

Question

我知道这个错误

每个组按表达式必须至少包含一个不是外部引用的列。

在运行此查询时：

SELECT TOP 1 
    SUM(mla.total_current_attribute_value)
FROM 
    partstrack_machine_location_attributes mla (NOLOCK) 
INNER JOIN 
    #tmpInstallParts_Temp installpartdetails ON mla.machine_sequence_id = installpartdetails.InstallKitToMachineSequenceId 
                                   AND (CASE WHEN mla.machine_side_id IS NULL THEN 1  
             WHEN  mla.machine_side_id = installpartdetails.InstallKitToMachineSideId THEN 1 END
    ) = 1
INNER JOIN 
    partstrack_mes_attribute_mapping mam (NOLOCK) ON mla.mes_attribute = mam.mes_attribute_name
INNER JOIN 
    partstrack_attribute_type at (NOLOCK) ON mam.pt_attribute_id = at.pt_attribute_id 
INNER JOIN 
    partstrack_ipp_mes_attributes ima(NOLOCK) ON at.pt_attribute_id = ima.pt_attribute_id                                                   
WHERE 
    mla.active_ind = 'Y' AND
    ima.ipp_ID IN (SELECT ipp.ipp_id
                   FROM partstrack_individual_physical_part ipp
                   INNER JOIN #tmpInstallParts_Temp tmp ON (ipp.ipp_id = tmp.InstallingPartIPPId OR
               (CASE WHEN tmp.InstallingPartIPKId = '-1' THEN 1 END) = 1
                  )
GROUP BY 
    ima.ipp_id

有人能帮我吗？

Answer 1

这是问题的第一次修订后的查询文本。在以后的修订中，您删除了最后一个结束括号)，查询在语法上变得不正确。您最好检查并修复问题的文本，并格式化查询文本，这样就可以阅读了。

SELECT TOP 1
    SUM(mla.total_current_attribute_value)
FROM
    partstrack_machine_location_attributes mla (NOLOCK)
    INNER JOIN #tmpInstallParts_Temp installpartdetails 
        ON mla.machine_sequence_id = installpartdetails.InstallKitToMachineSequenceId 
        AND (CASE WHEN mla.machine_side_id IS NULL THEN 1
            WHEN mla.machine_side_id = installpartdetails.InstallKitToMachineSideId THEN 1 END) = 1
    INNER JOIN partstrack_mes_attribute_mapping mam (NOLOCK) ON mla.mes_attribute = mam.mes_attribute_name
    INNER JOIN partstrack_attribute_type at (NOLOCK) ON mam.pt_attribute_id = at.pt_attribute_id
    INNER JOIN partstrack_ipp_mes_attributes ima(NOLOCK) ON at.pt_attribute_id = ima.pt_attribute_id
WHERE
    mla.active_ind = 'Y' 
    AND ima.ipp_ID IN 
    (
        Select
            ipp.ipp_id
        FROM
            partstrack_individual_physical_part ipp
            INNER JOIN #tmpInstallParts_Temp tmp 
                ON (ipp.ipp_id = tmp.InstallingPartIPPId 
                OR (CASE WHEN tmp.InstallingPartIPKId = '-1' THEN 1 END) = 1)
        GROUP BY
            ima.ipp_id
    )

有了这种格式设置，现在可以清楚地看到有一个带有GROUP BY的子查询。

很可能这只是一个错误:您打算编写GROUP BY ipp.ipp_id而不是GROUP BY ima.ipp_id。

如果您真的希望GROUP BY不是在子查询中，而是在主SELECT中，那么您就会错误地放置结束括号)，并且查询应该如下所示：

SELECT TOP 1
    SUM(mla.total_current_attribute_value)
FROM
    partstrack_machine_location_attributes mla (NOLOCK)
    INNER JOIN #tmpInstallParts_Temp installpartdetails 
        ON mla.machine_sequence_id = installpartdetails.InstallKitToMachineSequenceId 
        AND (CASE WHEN mla.machine_side_id IS NULL THEN 1
            WHEN mla.machine_side_id = installpartdetails.InstallKitToMachineSideId THEN 1 END) = 1
    INNER JOIN partstrack_mes_attribute_mapping mam (NOLOCK) ON mla.mes_attribute = mam.mes_attribute_name
    INNER JOIN partstrack_attribute_type at (NOLOCK) ON mam.pt_attribute_id = at.pt_attribute_id
    INNER JOIN partstrack_ipp_mes_attributes ima(NOLOCK) ON at.pt_attribute_id = ima.pt_attribute_id
WHERE
    mla.active_ind = 'Y' 
    AND ima.ipp_ID IN 
    (
        Select
            ipp.ipp_id
        FROM
            partstrack_individual_physical_part ipp
            INNER JOIN #tmpInstallParts_Temp tmp 
                ON (ipp.ipp_id = tmp.InstallingPartIPPId 
                OR (CASE WHEN tmp.InstallingPartIPKId = '-1' THEN 1 END) = 1)
    )
GROUP BY
    ima.ipp_id

在任何情况下，对源代码进行适当的格式化确实会有所帮助。

Answer 2

Group By ima.ipp_id 

应适用于外部查询。因为“(”)的位置不正确，所以它适用于内部查询。

现在，在更正查询之后，它可以正常工作，没有任何问题。

最后的查询是：

SELECT TOP 1 SUM(mla.total_current_attribute_value) FROM partstrack_machine_location_attributes mla (NOLOCK) INNER JOIN #tmpInstallParts_Temp installpartdetails ON mla.machine_sequence_id = installpartdetails.InstallKitToMachineSequenceId AND (CASE WHEN mla.machine_side_id IS NULL THEN 1 WHEN mla.machine_side_id = installpartdetails.InstallKitToMachineSideId THEN 1 END ) = 1 INNER JOIN partstrack_mes_attribute_mapping mam (NOLOCK) ON mla.mes_attribute = mam.mes_attribute_name INNER JOIN partstrack_attribute_type at (NOLOCK) ON mam.pt_attribute_id = at.pt_attribute_id INNER JOIN partstrack_ipp_mes_attributes ima(NOLOCK) ON at.pt_attribute_id = ima.pt_attribute_id WHERE mla.active_ind = 'Y' AND ima.ipp_ID IN ( Select ipp.ipp_id FROM partstrack_individual_physical_part ipp INNER JOIN #tmpInstallParts_Temp tmp ON (ipp.ipp_id = tmp.InstallingPartIPPId OR (CASE WHEN tmp.InstallingPartIPKId = '-1' THEN 1 END ) =1) ) GROUP BY ima.ipp_id

谢谢你们所有人。