MS SQL Server 实战排查多列之间的值是否重复

需求

在日常的应用中，排查列重复记录是经常遇到的一个问题，但某些需求下，需要我们排查一组列之间是否有重复值的情况。比如我们有一组题库数据，主要包括题目和选项字段（如单选选择项或多选选择项），一个合理的数据存储应该保证这些选项列之间不应该出现重复项目数据，比如选项A不应该和选项B的值重复，选项B不应该和选项C的值重复，以此穷举类推，以保证这些选项之间不会出现重复的值。本文将介绍如何利用 group by 、having 语句来实现这一需求，主要实现如下功能：

（1）上传 EXCEL 版试题题库到 MS SQL SERVER 数据库进行导入

（2）通过 union all 将各选项列的数据进行转记录行的合并

（3）通过 group by 语句和 count 聚合函数统计重复情况

（4）通过 having 子句筛选出重复记录

范例运行环境

操作系统： Windows Server 2019 DataCenter

数据库：Microsoft SQL Server 2016

.netFramework 4.7.2

数据样本设计

假设有 EXCEL 数据题库如下：

如图我们假设设计了错误的数据源，第4题的A选项与D选项重复，第8题的A选项与C选项重复了。

题库表 exams 设计如下：

序号	字段名	类型	说明	备注
1	sortid	int	排序号	题号，唯一性
2	etype	nvarchar	试题类型	如多选、单选
3	etitle	nvarchar	题目
4	A	nvarchar	选项A
5	B	nvarchar	选项B
6	C	nvarchar	选项C
7	D	nvarchar	选项D

功能实现

上传EXCEL文件到数据库

导入功能请参阅我的文章《C#实现Excel合并单元格数据导入数据集》这里不再赘述。

SQL语句

首先通过 UNION ALL 将A到D的各列的值给组合成记录集 a，代码如下：

代码语言：javascript复制

	select A as item,sortid from exams  
	 union all
	select B as item,sortid from exams  
	 union all
	select C as item,sortid from exams  
	 union all
	select D as item,sortid from exams

其次，通过 group by 对 sortid (题号) 和 item (选项) 字段进行分组统计，使用 count 聚合函数统计选项在题号中出现的个数，如下封装：

代码语言：javascript复制

select item,count(item) counts,sortid from (
	select A as item,sortid from exams  
	 union all
	select B as item,sortid from exams  
	 union all
	select C as item,sortid from exams  
	 union all
	select D as item,sortid from exams  
) a group by sortid,item order by sortid

最后使用 having 语句对结果集进行过滤，排查出问题记录，如下语句：

代码语言：javascript复制

select item,count(item) counts,sortid from (
	select A as item,sortid from exams  
	 union all
	select B as item,sortid from exams  
	 union all
	select C as item,sortid from exams  
	 union all
	select D as item,sortid from exams  
) a group by sortid,item   having count(item)>1 order by sortid

在查询分析器运行SQL语句，显示如下图：

由此可以看出，通过查询可以排查出第4题和第8题出现选项重复问题。

小结

我们可以继续完善对结果的分析，以标注问题序号是哪几个选项之间重复，可通过如下语句实现：

代码语言：javascript复制

select case when A=item then 'A' else ''end 
case when B=item then 'B' else '' end  
case when C=item then 'C' else '' end  
case when D=item then 'D' else '' end tip
,b.* from  
(select item,count(item) counts,sortid from (
	select A as item,sortid from exams  
	 union all
	select B as item,sortid from exams  
	 union all
	select C as item,sortid from exams  
	 union all
	select D as item,sortid from exams  
) a group by sortid,item   having count(item)>1 ) b,exams c where b.sortid=c.sortid

关键语句：case when A=item then 'A' else ''end

case when B=item then 'B' else '' end

case when C=item then 'C' else '' end

case when D=item then 'D' else '' end tip

这个用于对比每一个选项列，得到对应的选项列名，运行查询分析器，结果显示如下：