业务场景:
有两张表,部门表和员工表
部门表:
部门编码 部门名称
1 A
2 A
3 A
4 B
员工表:
员工名称 所属部门
张三 1
李四 1
王五 1
赵六 2
现在需要一个部门人员报表,要求将该部门的员工合并显示在一列,如:
部门编码 部门名称 所属人员
1 A 张三、李四、王五
2 B 赵六
解决方案:
通过SQLSERVER的 FOR XML PATH函数 + STUFF函数,对查询出来的数据进行合并拼接,放到指定的字段
select STUFF((select ',' + emp.name from t_employee emp where emp.dept_id = dept.id for xml path('')),1,1,'') as emp_names from t_Department dept
解析:
一、
FOR XML PATH ("行名称") 将查询结果集以XML形式展现,将多行的结果,展示在同一行
<row> --第一行
<列1名称>列1内容</列1名称> --第一列
<列2名称>列2内容</列2名称>
……
</row>
<row> --第二行
<列1名称>列1内容</列1名称> --第一列
<列2名称>列2内容</列2名称>
……
</row>
二、
STUFF ( expression , start , length ,replacewith)
STUFF 函数将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符;然后将第二个字符串插入到第一个字符串的开始位置。
1、expression
字符数据的表达式。expression 可以是常量、变量,也可以是子查询的字段
2、start
start用来指定删除和插入开始位置的数值。 如果 start 值为负或为零,则返回空字符串。 如果 start 的长度大于第一个 expression ,则返回空字符串。start 的类型也可以是 bigint。
注意:SQLSERVER 从1开始,start 值1 表示第一个字符。
3、length
length用来指定要删除的字符个数。 如果 length值 为负,则返回空字符串。 如果 length 的长度大于第一个 character_expression,则最多可以删除到最后一个 character_expression 中的最后一个字符。 如果 length 为零,则插入在 start 位置发生,并且不会删除任何字符。length 的类型也可以是 bigint。
4、repacewith
替换字符表达式。同样,也可以是常量、变量,也可以是字段或二进制字段。
从 start 开始替换 length 个字符的 expression 。 如果 repacewith为 NULL,则在不插入任何内容的情况下删除字符。
5、返回类型
如果 expression 是字符数据类型,则返回字符数据。
如果 expression 是二进制数据类型,则返回二进制数据。
注意:
expression 如果是子查询语句,则需要在查询结果加上一个字符,如上图例子,否则执行的结果会带出查询的字段名:
name >张三</name ><name >李四</name >
这是因为:直接查询字段,FOR XML PATH会默认返回字段名作为列名称,加上','之后,该列就没有名称,那么自然返回的就是,张三,李四,...此时,再用STUFF函数去掉第一个逗号,即可
如果有讲错或者疏漏的地方,还望指点!
更多推荐
SQLSERVER将子查询数据合并拼接成一个字段
发布评论