带有SUM、ORDER和有限值的SQL查询

Question

为了取得预期的结果，我遇到了麻烦。

我已经成功地连接了两个表(数据库是Server)，但是我想要更多的内容。

Excel -它的表格，其中包含的PartNumbers(GBC)列表与相应的Quantity这部分需要的构建。

我将把这个Excel与我的Inventory数据库连接起来，以检索关于我所拥有的、缺少什么、需要购买什么的信息。

当前查询：

string sqlCheck = @"SELECT e.GBC, e.Replaced, e.Description, Barcode, Location, Bookstand, Quantity, Buildneed, p.Quantity - e.Buildneed as Afterbuild FROM Parts p Right JOIN Excel e ON e.GBC = p.GBC ORDER BY GBC ASC, Quantity DESC";

如下图所示：

​

​

它需要使用所有重复的GBC，但按特定顺序。

首先，我需要使用GBC(86911)和Quantity = 100，这应该会导致Afterbuild = 0。

但是在列Buildneed中，我看到总共需要768，所以下一步是使用GBC(86911)和quantity = 500，这应该会导致Afterbuild 0，在这一行中，我希望看到新的列名为Totals，它将等于-168 (这意味着我需要购买这部分的168台pcs )。

1. I可以有许多相同的部件，具有不同数量的
2. ，如果Buildneed值将超过

，则我总是希望从最低数量开始在特定复制的上显示更多的GBC。

预期产出如下所示：

我增加了列'UseInOrder‘--它不是苗圃，但如果可能的话，那将是很棒的，它将指出我将需要使用从每个部分的顺序。

​

​

如下表所示，定义如下：

CREATE TABLE [dbo].[Excel] (
    [GBC]         INT          NULL,
    [Description] VARCHAR (50) NULL,
    [Buildneed]   INT          NULL,
    [Replaced]    VARCHAR (50) NULL
);

CREATE TABLE [dbo].[Parts] (
    [Barcode]       INT          IDENTITY (201900001, 1) NOT NULL,
    [GBC]           INT          NULL,
    [Description]   VARCHAR (50) NULL,
    [Location]      VARCHAR (50) NULL,
    [Bookstand]     VARCHAR (50) NULL,
    [Value]         VARCHAR (50) NULL,
    [Quantity]      INT          NULL,
    [MQuantity]     INT          NULL,
    [Manufacturer1] VARCHAR (50) NULL,
    [MPN1]          VARCHAR (50) NULL,
    [Manufacturer2] VARCHAR (50) NULL,
    [MPN2]          VARCHAR (50) NULL,
    [Manufacturer3] VARCHAR (50) NULL,
    [MPN3]          VARCHAR (50) NULL,
    CONSTRAINT [PK_Parts] PRIMARY KEY CLUSTERED ([Barcode] ASC)
);

编辑示例数据

declare @tblParts table(
  GBC int,
  Barcode varchar(256),
  [Location] varchar(256),
  Quantity int
)

declare @tblPartsUsed table(
  GBC int,
  Replaced varchar(1) default '',
  [Description] varchar(50),
  Buildneed int
)

insert into @tblParts (GBC,[Description], Barcode, [Location], Quantity)
select 86911, 'CAP_CER,10nF,0603,10%,100V,X7R' ,201901200, 'JD-01/  14' ,500
union all
select 86911, 'CAP_CER,10nF,0603,10%,100V,X7R' ,201901166, 'ESB-03' ,100
union all
select 99529, 'DIO_ZENR,5,6V,2%,MM3Z5V6ST1G,SOD323' ,201901024, 'ESB-01' ,100
union all
select 128082, 'CAP_CER,100nF,0603,10%,50V,X7R, poly' ,201901120, 'JD-01/  3' ,500
union all
select 128082, 'CAP_CER,100nF,0603,10%,50V,X7R, poly' ,201901121, 'JD-01/  3' ,500
union all
select 168078, 'CAP_CER,470nF,0805,10%,50V,X7R' ,201901207, 'JD-01/  19' ,170
union all
select 168078, 'CAP_CER,470nF,0805,10%,50V,X7R' ,201901152, 'ESB-03' ,140
union all
select 196881, 'BJT,C,SMBT3946DW1T1G,SOT363' ,201901085, 'ESB-02' ,100
union all
select 199296, 'BJT_DIG,C,SMUN5311DW1T1G,SOT363' ,201901083, 'ESB-02' ,100
union all
select 207735, 'DIO_LED, NFSA123DT' ,201902132, 'KRK' ,10


insert into @tblPartsUsed(GBC, [Description], Buildneed)
select 71744, 'RES_TF,10k,0402,1%,0,1W,100PPM/C' ,192
union all
select 71746, 'RES_TF,10k,0603,1%,0,1W,100PPM/C' ,168
union all
select 76527, 'CAP_CER,10nF,0402,10%,50V,X7R' ,288
union all
select 86911, 'CAP_CER,10nF,0603,10%,100V,X7R' ,1464
union all
select 92854, 'RES_TF,30k,0603,1%,0,1W,100PPM/C' ,72
union all
select 93018, 'RES_TF,68k,0603,1%,0,1W,100PPM/C' ,72
union all
select 95241, 'RES_TF,2k2,0402,1%,0,1W,100PPM/C' ,192
union all
select 95549, 'RES_TF,47k,0603,1%,0,1W,100PPM/C' ,72
union all
select 99529, 'DIO_ZENR,5,6V,2%,MM3Z5V6ST1G,SOD323' ,72
union all
select 112117, 'RES_TF,2k2,0603,1%,0,1W,100PPM/C' ,96
union all
select 126486, 'RES_TF,0R,0603' ,24
union all
select 128082, 'CAP_CER,100nF,0603,10%,50V,X7R, poly' ,72
union all
select 168078, 'CAP_CER,470nF,0805,10%,50V,X7R' ,72
union all
select 196200, 'BJT_DIG,N,PDTC114EU,SOT323' ,72
union all
select 196881, 'BJT,C,SMBT3946DW1T1G,SOT363' ,144
union all
select 199296, 'BJT_DIG,C,SMUN5311DW1T1G,SOT363' ,504
union all
select 199302, 'RES_TF,100R,0603,10%,0,1W,200PPM/C' ,72
union all
select 202047, 'UNI,N,PMPB215ENEA,DFN2020MD-6' ,72
union all
select 202054, 'DIO_LED,SPMWHT346EA3' ,648
union all
select 203509, 'CONN_HEADER,MOLEX,5023521100' ,24
union all
select 207735, 'DIO_LED, NFSA123DT' ,648
union all
select 207843, 'Thermistor,10k,0603,1%,NTC' ,24
union all
select 208252, 'FOOTPRINT_BOARD-IN,MOLEX,350220011' ,48
union all
select 212145, 'DIO_SIGN,200V,250mA,50nS,BAV21WSQ-7-F,SOD323' ,72

根据这一日期，输出应类似于下表图像：

​

​

Answer 1

尝尝这个

EDIT2 (更改初始数据后)

SELECT distinct u.GBC,p2.Replaced, p2.Description, IIF(u.Afterbuild>0, MIN(p1.Barcode) OVER(PARTITION BY u.GBC), p1.Barcode) as Barcode, p1.Location, u.Quantity, u.Buildneed, 
CASE
  when (u.Total <0 AND p1.Barcode is NULL) OR (SUM(u.Quantity) OVER(PARTITION BY u.GBC) - u.Buildneed)>0 then u.Afterbuild
  else 0
end
as Afterbuild,
CASE
   when u.Total <0 AND p1.Barcode is NULL then ABS(u.Total)
   when (SUM(u.Quantity) OVER(PARTITION BY u.GBC) - u.Buildneed)>0 then u.Total
   else u.Total
END as Total FROM
(
    SELECT distinct b.GBC, b.Quantity, b.Buildneed, 
       case 
           when b.Total <0 then null
           when b.Total >=0 AND MIN(b.Quantity) OVER(PARTITION BY b.GBC) - b.Buildneed < 0 AND b.Quantity = MAX(b.Quantity) OVER(PARTITION BY b.GBC) then SUM(b.Quantity) OVER(PARTITION BY b.GBC) - b.Buildneed
           when b.Total >=0 AND MIN(b.Quantity) OVER(PARTITION BY b.GBC) - b.Buildneed >= 0 AND b.Quantity = MIN(b.Quantity) OVER(PARTITION BY b.GBC) then MIN(b.Quantity) OVER(PARTITION BY b.GBC) - b.Buildneed
       END AS Afterbuild,
       case 
           when b.Total >=0 then null
           when b.Total <0 AND b.RowNumber = MAX(b.RowNumber) OVER(PARTITION BY b.GBC) then b.Total 
       END AS Total
    FROM
    (
        select r.GBC, r.Quantity, r.Buildneed,
        (SUM(r.Quantity) OVER(PARTITION BY r.GBC)) - r.Buildneed as Total, ROW_NUMBER() OVER(order by r.GBC, r.Quantity) as RowNumber
        from 
        (
            SELECT e.GBC, ISNULL(Quantity,0) as Quantity, ISNULL(Buildneed,0) as Buildneed FROM @tblParts p RIGHT JOIN @tblPartsUsed e ON e.GBC = p.GBC
        ) as r
    ) as b
) as u 
left join @tblParts as p1 on u.GBC = p1.GBC and ISNULL(u.Quantity,0) = ISNULL(p1.Quantity,0)
left join @tblPartsUsed as p2 on u.GBC = p2.GBC and ISNULL(u.Buildneed,0) = ISNULL(p2.Buildneed,0)
where (u.Afterbuild is not null or u.Total is not null or u.Quantity - u.Buildneed < 0)
order by u.GBC, u.Quantity

Answer 2

如per @RomaRuzich所述，这个问题需要一种表格格式的预期结果。

此外，还需要用一些数据说明零件和Excel表格结构来澄清这个问题。

做了一些假设并创建了具有输出结果的脚本。

declare @tblParts table(
                        GBC int, 
                        Barcode varchar(20), 
                        [Location] varchar(20), 
                        Bookstand varchar(10) default '',
                        Quantity int
                        )

declare @tblPartsUsed table(
                        GBC int, 
                        Replaced varchar(1) default '', 
                        [Description] varchar(50), 
                        Buildneed int
                        )

insert into @tblParts(GBC, Barcode, [Location], Quantity)
select 72223, '', '', 0
union all
select 86911, '201901200','JD-01/',500
union all
select 86911, '201901166','JD-01/ 14', 100

insert into @tblPartsUsed(GBC, [Description],  Buildneed)
select '72223', 'RES_TF', 60
union all
select '86911', 'CAP_CER, 10nf,0603', 768
union all
select '86911', 'CAP_CER, 10nf,0603', 768

SELECT distinct e.GBC, e.Replaced, e.[Description], Barcode, [Location],
 Bookstand, Quantity, Buildneed,
 p.Quantity - e.Buildneed as Afterbuild,
 x.TotalQuantity - Buildneed as Totals
FROM @tblParts p Right JOIN @tblPartsUsed e ON e.GBC = p.GBC 
    left join (select GBC, sum(Quantity) TotalQuantity from @tblParts group by GBC) x <br/>on e.GBC = x.GBC
ORDER BY e.GBC ASC, p.Quantity ASC

​

​