如何使用server 2016比较基于两个关键列的单个列的数据？

Question

输入

 Material    code   description    Name       Process 
   A            1       Test          BioKit     Mixing 
   A            2       Test          BioKit     Mixing    
   A            3       Test                     Mixing    
   A            4       Test123       BioKit     Mixing    
   B            1       Test          BioKit     GRINDING 
   B            2       Test          BioKit234  MixinG   

我有一个表，以代码和材料作为每种材料的关键列，代码在这里可用，我需要比较各个列的数据，假设资料A相同，3条记录的描述是相同的，4条记录值是不同的，那么我们需要在下面显示所有记录的输出名称是相同的，即使它包含空白和进程也是一样的

对于材料B描述是相同的，没有区别，对于名称字段，两个不同的值，那么我们需要显示下面的输出

过程也有不同的价值

输出

Material, Description ,  Name ,Process ,code1 ,  code2 , code3, code4 
A          Test123,            ,        ,       ,       ,       , 4 
B       ,           , BioKit234,Mixing  ,       ,   2    ,      ,

请您给我一个提示，如何根据列的材料和代码比较单个表中的单个列数据？

Answer 1

如果您想获得具有最高代码的材料，可以编写如下内容：

WITH HighestCode(Material, Code) AS (SELECT Material, MAX(code) FROM Table2 GROUP BY Material)
SELECT t.* FROM HighestCode hc INNER JOIN Table2 t ON hc.Material = t.Material AND hc.Code = t.Code;

这会导致

Material Code Description Name      Process
-------- ---- ----------- --------- -------
B        2    Test        BioKit234 Mixing
A        4    Test123     BioKit    Mixing

Answer 2

我仍然不确定我是否理解你的意思，但这里应该指出正确的方向：

我使用一个物质视图来模拟一个物质表，但是如果您有一个，请使用它：

CREATE VIEW Material
AS
SELECT DISTINCT Material FROM Table2
GO

为每种材料的每个标准值创建一些助手视图：

CREATE VIEW StandardDescription
AS
WITH MostCommon(Material, Description, DescriptionCount) AS (SELECT Material, Description, COUNT(Description) FROM Table2 GROUP BY Material, Description)
SELECT mat.Material, (SELECT TOP 1 Description FROM MostCommon mc WHERE mc.Material = mat.Material ORDER BY DescriptionCount DESC) AS StandardDescription FROM Material mat
GO

CREATE VIEW StandardName
AS
WITH MostCommon(Material, Name, NameCount) AS (SELECT Material, Name, COUNT(Name) FROM Table2 GROUP BY Material, Name)
SELECT mat.Material, (SELECT TOP 1 Name FROM MostCommon mc WHERE mc.Material = mat.Material ORDER BY NameCount DESC) AS StandardName FROM Material mat
GO

CREATE VIEW StandardProcess
AS
WITH MostCommon(Material, Process, ProcessCount) AS (SELECT Material, Process, COUNT(Process) FROM Table2 GROUP BY Material, Process)
SELECT mat.Material, (SELECT TOP 1 Process FROM MostCommon mc WHERE mc.Material = mat.Material ORDER BY ProcessCount DESC) AS StandardProcess FROM Material mat
GO

确定非标准行：

CREATE VIEW NonStandardTextsPerMaterial
AS
SELECT t.Material, t.Code, t.Description, NULL AS Name, NULL AS Process FROM Table2 t INNER JOIN StandardDescription v ON t.Material = v.Material WHERE Description != StandardDescription
UNION
SELECT t.Material, t.Code, NULL AS Description, t.Name, NULL AS Process FROM Table2 t INNER JOIN StandardName v ON t.Material = v.Material WHERE Name != StandardName
UNION
SELECT t.Material, t.Code, NULL AS Description, NULL AS Name, t.Process FROM Table2 t INNER JOIN StandardProcess v ON t.Material = v.Material WHERE Process != StandardProcess
GO

将它们累加到同一行：

SELECT Material, Code, MAX(Description) AS Description, MAX(Name) AS Name, Max(Process) AS Process FROM NonStandardTextsPerMaterial GROUP BY Material, Code;
GO