SQL统计构成组合数、未构成组合数

/*先通过此段简述来了解下大概的处理过程一支钢笔组成所需零件和数量：2 钢笔V1-笔帽，1 钢笔V1-笔盖，1 钢笔V1-笔芯，1 钢笔V1-笔头现有零件：4 钢笔V1-笔帽，2 钢笔V1-笔盖，3 钢笔V1-笔芯，2 钢笔V1-笔头，1 水笔V1-笔头，1 笔油，2 贴纸问1：现有零件可以组成多少支钢笔？计算公式：现有零件数量/所需零件数量=Count 答案：最小一组的Count钢笔V1-笔帽：4/2=2钢笔V1-笔盖：2/1=2钢笔V1-笔芯：3/1=3钢笔V1-笔头：2/1=2水笔V1-笔头：1/0=0       笔油：1/0=0       贴纸：2/0=0最终构成（结果>0）：钢笔V1：2支 （根据笔的类型分组，2、2、3、2取最小值。）问2：未组成钢笔的零件有哪些，所剩数量分别是多少？计算公式：现有零件数量 - (现有零件数量/所需零件数量)*所需零件数量钢笔V1-笔帽：4-2*2=0钢笔V1-笔盖：2-2*2=0钢笔V1-笔芯：3-2*1=1钢笔V1-笔头：2-2*1=0水笔V1-笔头：1-0*1=1       笔油：1-0*1=1       贴纸：2-0*1=2未组成钢笔的零件有（结果>0）：钢笔V1-笔芯：1水笔V1-笔头：1笔油：1贴纸：2*/

if exists (select * from sysobjects where id = OBJECT_ID('[T_SuiteType]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [T_SuiteType]CREATE TABLE [T_SuiteType] ([SuiteTypeID] [int]  IDENTITY (1, 1)  NOT NULL,[SuiteTypeName] [varchar]  (100) NOT NULL)ALTER TABLE [T_SuiteType] WITH NOCHECK ADD  CONSTRAINT [PK_T_SuiteType] PRIMARY KEY  NONCLUSTERED ( [SuiteTypeID] )SET IDENTITY_INSERT [T_SuiteType] ONINSERT [T_SuiteType] ([SuiteTypeID],[SuiteTypeName]) VALUES ( 1,N'钢笔')INSERT [T_SuiteType] ([SuiteTypeID],[SuiteTypeName]) VALUES ( 2,N'水笔')SET IDENTITY_INSERT [T_SuiteType] OFFif exists (select * from sysobjects where id = OBJECT_ID('[T_Suite]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [T_Suite]CREATE TABLE [T_Suite] ([SuiteID] [int]  IDENTITY (1, 1)  NOT NULL,[SuiteNum] [varchar]  (100) NULL,[SuiteName] [varchar]  (100) NOT NULL,[IsEnable] [bit]  NULL DEFAULT (1),[SuiteTypeID] [int]  NULL,[RMBMin] [decimal]  (18,2) NULL,[RMBMax] [decimal]  (18,2) NULL)ALTER TABLE [T_Suite] WITH NOCHECK ADD  CONSTRAINT [PK_T_Suite] PRIMARY KEY  NONCLUSTERED ( [SuiteID] )SET IDENTITY_INSERT [T_Suite] ONINSERT [T_Suite] ([SuiteID],[SuiteNum],[SuiteName],[IsEnable],[SuiteTypeID],[RMBMin],[RMBMax]) VALUES ( 1,N'tj000000001',N'钢笔V1',1,1,60.00,80.00)INSERT [T_Suite] ([SuiteID],[SuiteNum],[SuiteName],[IsEnable],[SuiteTypeID],[RMBMin],[RMBMax]) VALUES ( 2,N'tj000000002',N'水笔V1',1,2,40.00,60.00)SET IDENTITY_INSERT [T_Suite] OFFif exists (select * from sysobjects where id = OBJECT_ID('[T_SuiteBom]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [T_SuiteBom]CREATE TABLE [T_SuiteBom] ([SuiteBomID] [int]  IDENTITY (1, 1),[SuiteID] [int],[F_ID] [int],[FittingName] [varchar] (100),[Amount] [int],[Price] decimal(18,2),[IsKey] [bit])ALTER TABLE [T_SuiteBom] WITH NOCHECK ADD  CONSTRAINT [PK_T_SuiteBom] PRIMARY KEY  NONCLUSTERED ( [SuiteBomID] )SET IDENTITY_INSERT [T_SuiteBom] ONINSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (1,2,11,'水笔V1-笔头',1,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (6,2,12,'水笔V1-笔帽',1,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (7,2,13,'水笔V1-笔芯',1,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (4,1,1,'钢笔V1-笔帽',2,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (5,1,2,'钢笔V1-笔盖',1,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (3,1,3,'钢笔V1-笔芯',1,20,0)INSERT [T_SuiteBom] ([SuiteBomID],[SuiteID],[F_ID],[FittingName],[Amount],[Price],[IsKey]) VALUES (2,1,4,'钢笔V1-笔头',1,20,0)SET IDENTITY_INSERT [T_SuiteBom] OFFif exists (select * from sysobjects where id = OBJECT_ID('[T_SalesBargain_List]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [T_SalesBargain_List]CREATE TABLE [T_SalesBargain_List] (LID [int] IDENTITY (1, 1),SBId [int],F_Id [int],FittingName [varchar] (100),Amount [int],[Price] decimal(18,2))INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,11,'水笔V1-笔头',2,20)--2/1=2    2-1*1=1INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,12,'水笔V1-笔帽',1,20)--1/1=1    1-1*1=0INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,13,'水笔V1-笔芯',1,20)--1/1=1    1-1*1=0INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,1,'钢笔V1-笔帽',4,20)--4/2=2     4-2*2=0INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,2,'钢笔V1-笔盖',2,20)--4/1=4     2-1*2=0INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,3,'钢笔V1-笔芯',3,20)--3/1=3     3-1*2=1INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,4,'钢笔V1-笔头',2,20)--2/1=2     2-1*2=0INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,100,'笔油',2,20)INSERT [T_SalesBargain_List] (SBId,F_Id,FittingName,Amount,[Price]) VALUES (1,101,'贴纸',2,20)

select * from [T_Suite]select * from [T_SuiteBom]select * from [T_SalesBargain_List]

/*根据表[T_SuiteBom]可知一支钢笔V1组成配件有 (    2个 钢笔V1-笔帽    1个 钢笔V1-笔盖    1 钢笔V1-笔芯    1个 钢笔V1-笔头)可以组成完整的钢笔V1组合称之为 支现有一张合同，合同包含的配件有(    2个 水笔V1-笔头    1个 水笔V1-笔帽    1个 水笔V1-笔芯    4个 钢笔V1-笔帽      2个 钢笔V1-笔盖      3个 钢笔V1-笔芯      2个 钢笔V1-笔头      2个 笔油       2个 贴纸   )我们现在要算出 合同里的配件可以组成多少支水笔或钢笔最终结果如下：成支部分的结果笔名称    数量  单位钢笔V1     2     套水笔V1     1     套没有组成支部分的结果配件名称        数量  单位钢笔V1-笔芯      1     件 水笔V1-笔头      1     件 笔油           2     件 贴纸           2     件 */

;WITH data_source AS(SELECT     LID,a.SuiteID,a.SuiteName,b.FittingName,b.F_ID,    ISNULL(b.Amount,0) as Bom_Amount,    ISNULL(c.Amount,0) as L_Amount,    c.PriceFROM [T_Suite] aJOIN [T_SuiteBom] b ON a.SuiteID = b.SuiteIDLEFT JOIN [T_SalesBargain_List] c ON b.F_ID = c.F_ID)SELECT * into #data_source FROM data_source--成支数量SELECT SuiteID,SuiteName,MIN(L_Amount/Bom_Amount) Amount FROM #data_source GROUP BY SuiteID,SuiteName--没有组成支部分的结果;with data_no as(    SELECT         a.LID,a.SuiteID,a.SuiteName,a.FittingName,a.F_ID,a.L_Amount - b.Amount * a.Bom_Amount as Amount,a.Price,1 TypeID    FROM #data_source a    JOIN     (        SELECT SuiteID,MIN(L_Amount/Bom_Amount) Amount FROM #data_source GROUP BY SuiteID     ) b ON a.SuiteID = b.SuiteID    WHERE a.L_Amount - b.Amount * a.Bom_Amount > 0 OR LID IS NULL)select LID,SuiteID,SuiteName,F_ID,FittingName,Amount,Price,1 TypeID from data_noUNION ALLselect LID,SBID,'',F_ID,FittingName,Amount,Price,2 TypeID from [T_SalesBargain_List] WHERE LID not in(select LID from #data_source)drop table #data_sourceDROP TABLE [T_SuiteType]DROP TABLE [T_Suite]DROP TABLE [T_SuiteBom]DROP TABLE [T_SalesBargain_List]