【SQL的学习笔记】列转行和查询结果转JSON

前言

在分组查询时，没被设定为分组依据的列需要使用聚合函数合并，一般情况下，使用各个数据库提供的聚合函数就足够了，而一些特殊的使用场景可能需要手动将一列的多行数据转成行，实现类似聚合函数的效果。

案例

现有如下两张表：Class（班级）、Student（学生），其关系如图：

两表内的数据如下：

两表连接查询代码如下：

--先查看连接的结果
SELECT
	*
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID

两表连接查询的结果如下：

在此基础上添加分组统计可以得到各个班级有多少学生
代码如下：

SELECT
	 [班级AID] = C.AID
	,[班级名称] = C.Name
	,[班主任] = C.TeacherHeadName
	,[学生数量] = ISNULL(COUNT(S.AID),0)
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID
GROUP BY C.AID,C.Name,C.TeacherHeadName

结果如下：

重点来了

在查询各班学生数量的基础上，同时查询各班的学生姓名列表。
如果将查询写成这样的话：

SELECT
	 [班级AID] = C.AID
	,[班级名称] = C.Name
	,[班主任] = C.TeacherHeadName
	,[学生数量] = ISNULL(COUNT(S.AID),0)
	,[学生们] = S.Name
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID
GROUP BY C.AID,C.Name,C.TeacherHeadName

会提示未聚合错误（无法将多行学生姓名合成一行数据显示）：

如果不使用分组查询的话那就不能统计学生数了，而且前面几列的信息就会重复显示，这样的查询结果会让后台调用者很难处理：

SELECT
	 [班级AID] = C.AID
	,[班级名称] = C.Name
	,[班主任] = C.TeacherHeadName
	--,[学生数量] = ISNULL(COUNT(S.AID),0)
	,[学生们] = S.Name
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID
--GROUP BY C.AID,C.Name,C.TeacherHeadName

结果如下：

像这种在使用分组查询统计了学生数的情况下，学生列表的多行数据需要聚合成一行显示，数据库内置的聚合函数就不够用了，这时可以考虑将多行的学生姓名信息转成json显示成一行。

解决

代码如下：

SELECT
	 [班级AID] = C.AID
	,[班级名称] = C.Name
	,[班主任] = C.TeacherHeadName
	,[学生数量] = ISNULL(COUNT(S.AID),0)
	,[学生们] = 
	(
		SELECT
			[学生姓名] = CONVERT(nvarchar(3),InsideS.Name)
		FROM Student InsideS
		WHERE InsideS.ClassAID = C.AID
		FOR JSON AUTO
	)
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID
GROUP BY C.AID,C.Name,C.TeacherHeadName

执行结果如下：

至此，笔记结束。
为了让结果里不要有难看的NULL（主要是JAVA接收不好处理），稍微优化一下上面的代码如下：

SELECT
	 [班级AID] = C.AID
	,[班级名称] = C.Name
	,[班主任] = C.TeacherHeadName
	,[学生数量] = ISNULL(COUNT(S.AID),0)
	,[学生们] = ISNULL--判断：若返回的json字符串为NULL，则转成指定的空字符串
	(
		(
			SELECT
				[学生姓名] = CONVERT(nvarchar(3),InsideS.Name)
			FROM Student InsideS
			WHERE InsideS.ClassAID = C.AID
			FOR JSON AUTO
		)
		,
		''--长度为0的字符串（空的字符串）
	)
FROM Class C
LEFT JOIN Student S ON S.ClassAID = C.AID
GROUP BY C.AID,C.Name,C.TeacherHeadName

其他可以不用看的提示

使用FOR JSON AUTO将查询结果转成JSON时，如果结果里具有歧义字符（比如我遇到了查询结果里有斜杠：/，转出来的JSON中会被自动添加转义符（反斜杠）：\），这里专门笔记一下当时查到的处理方法：

补充记录：
REPLACE(参数一，参数二，参数三）
参数一：原字符串（母串）
参数二：查找的待替换的字符串（子串）
参数三：希望替换成的字符串
比如上面的代码中，我在json字符串里查找’\’，并将‘\’替换成‘’(单引号里什么都没有，连空格都没有)