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

目录

需求

范例运行环境

数据样本设计

功能实现

上传EXCEL文件到数据库

SQL语句

小结

---

需求

在日常的应用中，排查列重复记录是经常遇到的一个问题，但某些需求下，需要我们排查一组列之间是否有重复值的情况。比如我们有一组题库数据，主要包括题目和选项字段（如单选选择项或多选选择项），一个合理的数据存储应该保证这些选项列之间不应该出现重复项目数据，比如选项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，代码如下：

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

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

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

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

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

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

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

小结

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

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 allselect B as item,sortid from exams  union allselect C as item,sortid from exams  union allselect 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

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

这样我们可以更直观的看到重复的选项列名是哪几个，以更有效帮助我们改正问题。在实际的应用中每一个环节我们都难免会出现一些失误，因此不断的根据实际的发生情况总结经验，通过计算来分析，将问题扼杀在摇篮里，以最大保证限度的保证项目运行效果的质量。

至此关于排查多列之间重复值的问题就介绍到这里，感谢您的阅读，希望本文能够对您有所帮助。