根据sqlserver中键/值对表中的值重复条目

我有一个键/值对表

Create Table mat_characteristics
(
 material varchar(100),
 characteristic varchar(100),
 characteristic_value varchar(100)
)
GO

Insert into mat_characterstics values
('113567','height','20.3'),
('113567','weight','11.3'),
('113567','diameter','6.3'),
('113567','length','6.3'),
('113564','height','20.3'),
('113564','length','2.3'),
('113564','length','6.3'),
('113565','height','20.3'),
('113565','weight','11.3'),
('113565','weight','2.3'),
('113565','weight','7.3'),
('113565','diameter','6.3'),
('113565','length','6.3'),
('113545','height','20.3'),
('113545','weight','11.3'),
('113545','weight','2.3'),
('113545','weight','7.3'),
('113545','diameter','6.3'),
('113545','length','6.3');

如果看到113565和113545材料具有相同的特性和特征值。根据我们的业务规则,任何两种材料都不应具有相同的特性和特征值。在我的真实案例中,材料有时会具有近1000种特性。

请帮助我确定不良记录。

我尝试使用此查询将行分成列,但这导致超过1300列,我不确定在此之后如何进行

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) -@ ColumnName用于存储最大列名数 声明@ColumnName AS NVARCHAR(MAX)

-创建临时表 选择*进入#tmpmat_characterstics 从 (从mat_characterstics的ColumnSequence中选择(材料,特征,特征值,特征+ cast(row_number()超过(按材料划分,特征按特征排序)作为varchar(45))作为ColumnSequence)mat_characterstics

选择@ ColumnName = COALESCE(@ ColumnName +',','')+ QUOTENAME(列序列) 从 ( 从#tmpmat_characterstics中选择不同的列顺序 )测试

设置@ DynamicPivotQuery =

从#tmpmat_characterstics中选择材料'+ @ ColumnName +' 枢 (     最大(characterstic_value)     用于('+ @ ColumnName +')中的ColumnSequence )PIV'

EXEC(@DynamicPivotQuery);

评论
  • cut
    cut 回复

    您可以使用索引视图在SQL Server中强制执行此操作。例如

    use tempdb
    drop table if exists mat_characteristics
    drop view if exists v_mat_mat_characterstics_enforce_uniqueness
    go
    Create Table mat_characteristics
    (
     material varchar(100),
     characteristic varchar(100),
     characteristic_value varchar(100)
    )
    GO
    
    Insert into mat_characteristics values
    ('113567','height','20.3'),
    ('113567','weight','11.3'),
    ('113567','diameter','6.3')
    
    go
    create or alter view v_mat_mat_characterstics_enforce_uniqueness
    with schemabinding
    as
    select material, characteristic, characteristic_value, COUNT_BIG(*) rows
    from dbo.mat_characteristics
    group by  material, characteristic, characteristic_value
    go
    create unique clustered index ci_v_mat_mat_characterstics_enforce_uniqueness
      on v_mat_mat_characterstics_enforce_uniqueness(characteristic, characteristic_value)
    go
    Insert into mat_characteristics values
    ('113567','length','6.3'),
    ('113564','height','20.3'),
    ('113564','length','2.3'),
    ('113564','length','6.3'),
    ('113565','height','20.3'),
    ('113565','weight','11.3'),
    ('113565','weight','2.3'),
    ('113565','weight','7.3'),
    ('113565','diameter','6.3'),
    ('113565','length','6.3'),
    ('113545','height','20.3'),
    ('113545','weight','11.3'),
    ('113545','weight','2.3'),
    ('113545','weight','7.3'),
    ('113545','diameter','6.3'),
    ('113545','length','6.3');
    

    第二个插入失败,并带有

    Msg 2601, Level 14, State 1, Line 29
    Cannot insert duplicate key row in object 'dbo.v_mat_mat_characterstics_enforce_uniqueness' with unique index 'ci_v_mat_mat_characterstics_enforce_uniqueness'. The duplicate key value is (diameter, 6.3).
    The statement has been terminated.
    
  • 幽明自若
    幽明自若 回复

    The simplest method is string_agg():

    select characteristics, count(*),
           string_agg(material, ',') as materials
    from (select material,
                 string_agg(concat(characteristic, ':', characteristic_value), ',') as characteristcs
          from mat_characteristics
          group by material
         ) m
    group by characteristics
    having count(*) > 1;