sql - 想要一行数据，但 group by 似乎没有这种影响

Question

我只想返回一行数据和数据总和，并按生产计划项目 ID 分组，理想情况下我想要的是一行显示该字段和总和数量值，因此它应该读取

ProductionPlanItemId    QtyCompleted
5865406                 3  

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [CompletedPrintedId]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      ,[ProductionPlanItemID]
      ,[SOPOrderReturnLineId]
FROM [CompletedPrinted]
where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
GROUP BY 
      [ProductionPlanItemID]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      , [CompletedPrintedId]
      ,[SOPOrderReturnLineId]

列模式是

CREATE TABLE [dbo].[CompletedPrinted](
    [CompletedPrintedId] [bigint] NOT NULL,
    [UserName] [nvarchar](66) NOT NULL DEFAULT (''),
    [StartDateTIme] [datetime] NULL,
    [EndDateTime] [datetime] NULL,
    [ProductionPlanId] [bigint] NOT NULL DEFAULT ((0)),
    [SopLineItemId] [nvarchar](64) NOT NULL DEFAULT (''),
    [Detail] [nvarchar](1002) NOT NULL DEFAULT (''),
    [isActive] [bit] NOT NULL DEFAULT ((0)),
    [DocumentNo] [nvarchar](102) NOT NULL DEFAULT (''),
    [StockCode] [nvarchar](32) NOT NULL DEFAULT (''),
    [StockDescription] [text] NOT NULL DEFAULT (''),
    [QtyCompleted] [bigint] NOT NULL DEFAULT ((0)),
    [SubAssembledQty] [bigint] NOT NULL DEFAULT ((0)),
    [QtyRequired] [bigint] NOT NULL DEFAULT ((0)),
    [ProductionPlanItemID] [bigint] NOT NULL DEFAULT ((0)),
    [SOPOrderReturnLineId] [bigint] NOT NULL DEFAULT ((0))
)

我在下面用示例数据创建了一个 sql fiddle。

http://sqlfiddle.com/#!18/8927c/2

编辑 2 对不起，我应该说我需要其他列。

标签： sqlsql-server

Answer 1

如果每个 ProductionPlanId 需要一行，请使用 row_number()

select * from (SELECT [CompletedPrintedId]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      ,[ProductionPlanItemID]
      ,[SOPOrderReturnLineId]
      ,row_number() over(partition by ProductionPlanId
                          order by QtyCompleted) rn
  FROM [CompletedPrinted]
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
) a where a.rn=1

演示链接

但似乎你只需要sum()

select    
      [ProductionPlanId]
      ,sum(QtyCompleted) 

  FROM [CompletedPrinted]
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
  group by ProductionPlanId

输出

 ProductionPlanId   QtyCompleted
  5865405             3

因为您也需要所有列，所以请在下面尝试

/****** 来自 SSMS 的 SelectTopNRows 命令脚本 ******/

 with cte as
  ( select    
     c.ProductionPlanId
      ,sum(QtyCompleted)  as QtyCompleted

  FROM [CompletedPrinted] c
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
  group by ProductionPlanId
    ) , cte2 as
    (select cte.QtyCompleted as MQtyCompleted,c2.*,
       row_number()over(partition by c2.ProductionPlanId order by cte.QtyCompleted) rn
       from
    cte join CompletedPrinted c2
    on cte.ProductionPlanId=c2.ProductionPlanId
     ) select * from cte2 where rn=1