如何使用sql 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