基于SQL Server中键/值对表中的值的重复条目

Question

我有一个键/值对表

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 is used to store the maximum number of column names
DECLARE @ColumnName AS NVARCHAR(MAX)
-- Creating temporary table
select * into #tmpmat_characterstics
from 
(select material, characterstic,characterstic_value,characterstic+cast(row_number() over (partition by material,characterstic order by characterstic) as varchar(45)) as ColumnSequence from mat_characterstics) mat_characterstics
select @ColumnName= COALESCE(@ColumnName+ ', ','') + QUOTENAME(columnsequence)
from 
(
select distinct columnsequence from #tmpmat_characterstics
) test

set @DynamicPivotQuery= 
'select material, ' +@ColumnName+ 'from #tmpmat_characterstics
PIVOT
(
max(characterstic_value)
for ColumnSequence in (' +@ColumnName+ ')
) PIV'

EXEC(@DynamicPivotQuery);

Answer 1

最简单的方法是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;

Answer 2

您可以在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.

Answer 3

我会先找到重复的，然后加入原始表来找出材料：

select mc.material, mc.characteristic, mc.characteristic_value
from (  --  find all duplicate sets of characteristic and characteristic_value
select characteristic, characteristic_value
from mat_characteristics
group by characteristic, characteristic_value
having count(*) > 1
) a
inner join mat_characteristics mc -- inner join the original table to get the materials
on mc.characteristic = a.characteristic
and mc.characteristic_value = a.characteristic_value
order by mc.characteristic, mc.characteristic_value, mc.material