时间戳

首页 > 解决方案 > 如何使用 sql server 2016 比较基于两个关键列的单个列的数据？

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

问题描述

输入

 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

我有一个表，其中代码和材料作为每种材料的关键列，代码将在此处可用我需要比较假设材料的各个列数据 3 条记录的描述相同，4 条记录值不同，然后我们需要显示下面的输出名称对于所有记录都是相同的，即使它包含空白并且处理也相同

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

输出

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

你能告诉我如何根据列材料和代码比较单个表中的各个列数据吗？

标签： sql-serversql-server-2016

解决方案

仍然不确定我是否理解正确，但这应该为您指明正确的方向：

我使用材质视图来模拟材质表，但如果您有，请使用：

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

推荐阅读