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文件到数据库
导入功能请参阅我的文章这里不再赘述。
SQL语句
首先通过 UNION ALL 将A到D的各列的值给组合成记录集 a，代码如下：
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 聚合函数统计选项在 题号 中出现的个数，如下封装：
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 语句对结果集进行过滤，排查出问题记录，如下语句：
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题出现选项重复问题。
小结
我们可以继续完善对结果的分析，以标注问题序号是哪几个选项之间重复，可通过如下语句实现：
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
这个用于对比每一个选项列，得到对应的选项列名，运行查询分析器，结果显示如下：
这样我们可以更直观的看到重复的选项列名是哪几个，以更有效帮助我们改正问题。在实际的应用中每一个环节我们都难免会出现一些失误，因此不断的根据实际的发生情况总结经验，通过计算来分析，将问题扼杀在摇篮里，以最大保证限度的保证项目运行效果的质量。
至此关于排查多列之间重复值的问题就介绍到这里，感谢您的阅读，希望本文能够对您有所帮助。
- 点赞
- 收藏
- 关注作者
评论（0）