【高手来】合并数量

[吕津]

1. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，其它栏位的信息可能不同。
2. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，合并Quantity
3. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，只需要返回任意一条记录，并且Quantity是合并后的数量。

USE [hamp]
GO
/****** 对象:  Table [dbo].[InventoryAllocationDocSub]    脚本日期: 07/06/2013 15:32:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InventoryAllocationDocSub](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DocId] [int] NOT NULL,
	[ProjectId] [int] NULL,
	[DetailIdOut] [int] NULL,
	[DetailIdIn] [int] NULL,
	[MaterialId] [int] NULL,
	[MaterialName] [varchar](128) COLLATE Chinese_PRC_CI_AS NULL,
	[HampooPN] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[SupplierPN] [varchar](384) COLLATE Chinese_PRC_CI_AS NULL,
	[ClientPN] [varchar](384) COLLATE Chinese_PRC_CI_AS NULL,
	[WarehouseId] [int] NULL,
	[ShelfId] [int] NULL,
	[WarehouseIdBack] [int] NULL,
	[ShelfIdBack] [int] NULL,
	[BatchId] [int] NULL,
	[PackageId] [int] NULL,
	[Standard] [varchar](256) COLLATE Chinese_PRC_CI_AS NULL,
	[Unit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Quantity] [decimal](19, 6) NULL,
	[SubUnit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[SubQuantity] [decimal](19, 6) NULL,
	[UnitRate] [decimal](19, 6) NULL,
	[SourceDoc] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
	[SaleOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[ProduceOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[PurchaseOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[MaterialPlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[ProducePlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[PurchasePlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[TaxPrice] [decimal](19, 6) NULL,
	[OrderCount] [decimal](19, 6) NULL,
	[SupplyType] [int] NULL,
	[OrderAddupCount] [decimal](19, 6) NULL,
	[AddupCount] [decimal](19, 6) NULL,
	[Status] [int] NOT NULL CONSTRAINT [DF__Inventory__Statu__65989063]  DEFAULT ((0)),
	[ProductionDate] [datetime] NULL,
	[ExpirationDate] [datetime] NULL,
	[ProductionDayCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Remark] [nvarchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
	[AddupCount1] [decimal](19, 6) NULL,
	[Status1] [int] NULL,
	[ExDeci1] [decimal](19, 6) NULL,
	[ExDeci2] [decimal](19, 6) NULL,
 CONSTRAINT [PK_INVENTORYALLOCATIONDOCSUB] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单据副表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Id'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单据表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DocId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProjectId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存明细表编号(出库)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DetailIdOut'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存明细表编号(入库)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DetailIdIn'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'MaterialId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'MaterialName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料编码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'HampooPN'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'供应商料号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SupplierPN'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户料号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ClientPN'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仓库编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'WarehouseId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'货架编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ShelfId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'批次编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'BatchId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分装编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'PackageId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规格' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Standard'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Unit'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数量' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Quantity'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'辅单位' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SubUnit'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'辅数量' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SubQuantity'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位换算率' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'UnitRate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'源单据副表编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SourceDoc'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'销售订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SaleOrderNo'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProduceOrderNo'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采购订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'PurchaseOrderNo'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产计划单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProducePlanOrderNo'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料来源类型(1，汉普采购；2，客供；3，汉普采购转客供)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SupplyType'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'副表状态' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Status'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProductionDate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'过期日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ExpirationDate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Remark'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调拨单据副表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub'

[lzw_0736]

select HampooPN,BatchId,WarehouseId,ShelfId,sum(Quantity) Quantity from InventoryAllocationDocSub group by HampooPN,BatchId,WarehouseId,ShelfId

[lzw_0736]

select b.*,a.Quantity as QTY_Total from ( select HampooPN,BatchId,WarehouseId,ShelfId,sum(Quantity) Quantity,min(id) id from InventoryAllocationDocSub group by HampooPN,BatchId,WarehouseId,ShelfId ) a inner join InventoryAllocationDocSub b on a.id=b.id