查询按项目分组的零件最小制造值时遇Msg116错误的解决方法

咱们先拆解下你遇到的Msg 116错误根源：你写的Qt Pole子查询，在SELECT语句里同时返回了DailySumPoteau.IdProject和MIN(DailySumPoteau.DailySum)两列数据，但主查询的这个位置只能接收单个值（一列一行）——就像给表格的一个单元格填数据，不能一下子塞两个内容进去，数据库自然会报错。

结合你的需求（按项目分组查询已制造零件的MIN值），我给你两种解决方案，同时还修正了一个隐性的关联错误：

方案1：修正原有子查询（仅返回所需的MIN值） #

调整子查询的SELECT部分，只保留你需要的最小值，同时确保子查询和主查询的项目正确关联，避免返回无关数据：

SELECT 
    proinfo.ProjectN 
    ,ProjShipp.[Parts] 
    ,ProjShipp.Qty AS 'Qty Total' 
    ,Sum(DailyProduction.Quantity) AS 'Qty Manufactured' 
    -- 子查询仅返回当前项目的每日生产总和最小值，去掉多余的IdProject列
    ,(SELECT MIN(DailySumPoteau.DailySum) 
      FROM (SELECT PShipp.IdProject, SUM(DailyWelding.Quantity) DailySum 
            FROM DailyWeldingPaintProduction DailyWelding 
            INNER JOIN ProjectShipping PShipp ON PShipp.id=DailyWelding.FK_idPartShip 
            -- 关联主查询的项目ID，确保只计算当前项目的最小值
            WHERE PShipp.IdProject = proinfo.id  
            GROUP BY PShipp.id,PShipp.IdProject) DailySumPoteau 
     ) AS 'Qt Pole' 
FROM [dbo].[DailyWeldingPaintProduction] DailyProduction 
INNER join ProjectShipping ProjShipp on ProjShipp.id=DailyProduction.FK_idPartShip 
-- 修正外键关联：原语句的IdProject未指定表，这里明确关联ProjectShipping的外键字段
inner join ProjectInfo proinfo on proinfo.id=ProjShipp.ProjectInfoId  
GROUP By proinfo.id ,proinfo.ProjectN ,ProjShipp.[Parts] ,ProjShipp.Qty ,ProjShipp.[Designation] ,ProjShipp.id

方案2：用CTE+JOIN替代子查询（逻辑更清晰） #

如果子查询嵌套太深，用CTE（公共表表达式）先分步计算，再通过JOIN关联到主查询，可读性和维护性更好：

-- 第一步：计算每个零件的每日生产总和
WITH DailySumCTE AS (
    SELECT 
        PShipp.IdProject,
        PShipp.id AS PartShipId,
        SUM(DailyWelding.Quantity) AS DailySum
    FROM DailyWeldingPaintProduction DailyWelding 
    INNER JOIN ProjectShipping PShipp ON PShipp.id = DailyWelding.FK_idPartShip 
    GROUP BY PShipp.IdProject, PShipp.id
),
-- 第二步：按项目分组取每日总和的最小值
ProjectMinSum AS (
    SELECT 
        IdProject,
        MIN(DailySum) AS MinDailySum
    FROM DailySumCTE
    GROUP BY IdProject
)
-- 主查询关联CTE获取结果
SELECT 
    proinfo.ProjectN 
    ,ProjShipp.[Parts] 
    ,ProjShipp.Qty AS 'Qty Total' 
    ,Sum(DailyProduction.Quantity) AS 'Qty Manufactured' 
    ,pms.MinDailySum AS 'Qt Pole'
FROM [dbo].[DailyWeldingPaintProduction] DailyProduction 
INNER JOIN ProjectShipping ProjShipp ON ProjShipp.id = DailyProduction.FK_idPartShip 
INNER JOIN ProjectInfo proinfo ON proinfo.id = ProjShipp.ProjectInfoId 
INNER JOIN ProjectMinSum pms ON pms.IdProject = proinfo.id
GROUP BY proinfo.id, proinfo.ProjectN, ProjShipp.[Parts], ProjShipp.Qty, ProjShipp.[Designation], ProjShipp.id, pms.MinDailySum

注意事项 #

• 请根据你的实际表结构调整外键字段名：如果ProjectShipping的外键是IdProject而非ProjectInfoId，替换对应字段即可。
• 两种方案都确保了子查询/CTE只返回单个最小值，符合主查询对列值的要求。

内容的提问来源于stack exchange，提问作者M.Bouabdallah