当前位置：首页 > 问答 > 正文

MSSQL里用符号来分列然后做数据分组，感觉挺实用的一个方法分享

颜泰平
问答
2025-12-30 05:43:01
2

今天想分享一个在MSSQL里处理数据时我觉得特别实用的小技巧，这个技巧的核心就是用一些特定的符号，比如逗号、分号或者竖线，把多个信息塞进一个字段里存起来，等到需要分析的时候，再把这个字段拆开，进行分组统计，听起来可能有点绕，我直接用一个例子来说明,你就明白了。

这个方法的灵感，其实来源于很多实际的工作场景，你可能会在一些老的业务系统里，或者为了图方便的设计中，看到一个字段的内容是像“苹果,香蕉,橙子”或者“101,205,308”这样的，这种存储方式虽然不符合数据库设计的规范（也就是第一范式），但在某些情况下它确实存在，而且我们不得不去处理它，我在一次处理用户标签数据时,就从同事那里学到了这个巧妙的方法。

假设我们有一张简单的表，叫UserInterests，它记录了用户的兴趣爱好，但是设计得很简单粗暴，每个用户的兴趣都用逗号连在一起，放在一个字段里,表的结构和数据大概是这样：

用户ID	用户姓名	兴趣爱好
1	张三	篮球,足球,音乐
2	李四	音乐,电影,读书,篮球
3	王五	足球,旅游
4	赵六	读书,音乐

我们想分析一下，哪个兴趣爱好最受欢迎，也就是统计每个兴趣出现的次数，按照常规思路，这个表根本没法直接分组统计，因为每个单元格里都是一堆东西，这时候，就需要用到“分列”的技巧了。

MSSQL里用符号来分列然后做数据分组，感觉挺实用的一个方法分享

在MSSQL中，我们主要依靠一个非常强大的系统函数叫STRING_SPLIT，这个函数是微软在SQL Server 2016及以后版本中引入的，专门用来干这个事，它的作用就是根据你指定的分隔符（比如逗号）,把一个字符串拆分成多行。

我们的查询语句可以这样写：

SELECT value AS Interest, COUNT(*) AS UserCount
FROM UserInterests
CROSS APPLY STRING_SPLIT(兴趣爱好, ',')
GROUP BY value
ORDER BY UserCount DESC;

我来解释一下这几行代码是怎么工作的：

MSSQL里用符号来分列然后做数据分组，感觉挺实用的一个方法分享

FROM UserInterests：这很好理解,就是从我们的原始表开始。
CROSS APPLY STRING_SPLIT(兴趣爱好, ',')：这是最关键的一步。CROSS APPLY可以理解为一种特殊的连接方式，它会把表里的每一行，都和应用右侧的函数STRING_SPLIT的结果进行连接。STRING_SPLIT(兴趣爱好, ',')的作用就是，针对每一行的“兴趣爱好”字段，按照逗号进行切割，切割后，它会返回一个临时的结果集，里面每一行都只包含一个兴趣。拿“张三”他的兴趣爱好“篮球,足球,音乐”经过STRING_SPLIT处理后会变成三行：
- 第一行：value = '篮球'
- 第二行：value = '足球'
- 第三行：value = '音乐' 通过CROSS APPLY，原来“张三”的这一行数据，就在查询中“膨胀”成了三行，每一行都保留了用户ID、姓名,但兴趣爱好字段变成了单个的值。
SELECT value AS Interest：这个value就是STRING_SPLIT函数切割后返回的单个兴趣项目的列名，我们给它起个别名叫Interest。
COUNT(*) AS UserCount：因为每个兴趣都独占一行了，我们就可以很方便地用COUNT(*)来统计每个兴趣出现了多少次。
GROUP BY value和ORDER BY UserCount DESC：按照兴趣分组,并按照用户数量从高到低排序。

执行上面的查询,我们大概会得到这样的结果：

Interest	UserCount
音乐	3
篮球	2
足球	2
读书	2
电影	1
旅游	1

看，结果一目了然，“音乐”是最受欢迎的兴趣爱好，我们成功地把一个“脏”数据变成了有价值的信息。

在使用这个方法的时候,有几点需要特别注意：

数据库版本：确保你的SQL Server是2016或更高版本，否则没有STRING_SPLIT函数，低版本虽然可以用其他方法实现（比如用XML路径）,但会复杂很多。
数据清洗：原始数据的分隔符前后可能有空格，篮球, 足球”，这样拆分出来的“足球”前面会带一个空格，导致“足球”和“足球”被当成两个不同的兴趣，为了避免这个问题，最好在切割前先用TRIM函数处理一下字段，或者更稳妥的是在存入数据库时就做好规范，我们可以把查询写得更健壮一点：
```
SELECT TRIM(value) AS Interest, COUNT(*) AS UserCount
FROM UserInterests
CROSS APPLY STRING_SPLIT(兴趣爱好, ',')
GROUP BY TRIM(value)
ORDER BY UserCount DESC;
```
分隔符一致性：要确保分隔符在整个字段中是统一的且正确的，不能有些地方用逗号，有些地方用分号，或者有的值里本身就包含逗号（比如地址信息）,那就会拆分出错误的结果。

虽然这种用符号分隔的存储方式并不被推荐为数据库设计的最佳实践，因为它违反了原子性原则，会带来更新、删除和查询的麻烦，但在现实世界中，我们常常需要面对这种既成的数据结构，学会这个用STRING_SPLIT分列再分组的方法，就像手里多了一把瑞士军刀，能在很多看似棘手的数据处理问题上，快速高效地找到答案，它特别适用于快速生成标签云、分析调查问卷的多选题结果、处理日志文件等场景,希望这个具体的小例子能给你带来启发。