数据库多行合并

1）内容：将原本数据，根据某个字段内容进行分类，然后将相同类型的数据合并为一行显示。

2）函数：

2.1)

数据库：SQL SERVER2008

函数：for xml path

注意：sql server2005版本后才支持，在sql server2000是不支持的，亲测，但可以通过自定义函数替代

替代函数代码：

declare @s varchar(8000);
set @s = '';

select @s = @s + field1 + field2 + field3
from #temp
where ...
order by ...;
select @s;

 参考：stack overflow：SQL Server 2000 XML PATH('') error

3）效果例子：

数据库：SQL SERVER2008

原表数据：

select * from pubDept

效果：（图一）

3.1）简单合并sql：（将所有结果合并）

select DeptName + ',' from pubDept
for xml path('')

效果，图二：

3.2）根据类型分类，并合并一行sql：（注：根据公司编号，将相同公司的部门名称合并为一行显示）

select CompNo, (select DeptName + ',' from pubDept t1 where t1.CompNo = t2.CompNo for xml path('')) as DeptNames
from pubDept t2
--这里用于归类，并防重，不然每一行都有拼接结果
group by compNo

效果，图三：（注：这里只有一个公司）

PS：若不加group by，会导致有重复数据，因为原本每行都有公司编号为'CF001'，所以都符合，为了明显展示缘由，我这里增加了pubDept表主键DeptId以便理解，如下图

代码：

select DeptId,CompNo, (select DeptName + ',' from pubDept t1 where t1.CompNo = t2.CompNo for xml path('')) as DeptNames
from pubDept t2

效果，图四：（可跟图一对比）

 

4）for xml path函数实战分析

4.1）说明：for xml path函数，放在from table后面，其中，path后可自定义标签，这里的标签是指每一行的根标签，而查询出来的字段名称，则对应为根标签下各个属性的标签名称。

4.2）情况1：

代码：（path后，不加内容）

select DeptName from pubDept for xml path

效果，图五：（这里两行，可能是因为数据量的问题，不考究）

数据结构：

<row>
    <DeptName>
        中医科
    </DeptName>
</row>

分析：每一行的根标签，默认用<row>，而根元素下的各个属性，则跟属性名称一致

4.2）情况2

代码：（path后面添加内容，字段属性名称也替换）

select DeptName '部门名称' from pubDept for xml path('根节点')

效果，图六：

数据结构：

<根节点>
    <部门名称>
        中医科
    </部门名称>
</根节点>

分析：这里，将原来默认的<row>替换成自定义的根标签，而节点名称也一样替换掉

4.3）引伸思考：只要属性内容，节点名称，包括根节点，全部去掉

思路：将所有节点标签，全替换成空字符，即，将根节点（path后内容），属性节点（属性名称）去掉

代码：

select DeptName '' from pubDept for xml path('')

效果，图七：（报错）

报错内容提示：[Err] 42000 - [SQL Server]缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请将别名更改为有效名称。

修改后代码：（可用其他字符拼接）

select DeptName + '' from pubDept for xml path('')

效果，图八：（不易于观察）

优化代码：

select DeptName + ',' from pubDept for xml path('')

效果：（如图二）

 

 

5）其他合并方法：

数据库：SQL SERVER2008

未合并时，代码和效果：（图九）

 

合并时，代码：

DECLARE @sql_col VARCHAR(500)
--stepNo为数字类型，这里才要用convert函数转换，但isnull必须用，估计内部机制用了迭代
SELECT @sql_col = isnull(@sql_col + '->','') + CONVERT(nvarchar(50),StepNo) FROM ApproveFlowStep_
select @sql_col

DECLARE @sql_col1 VARCHAR(500)
SELECT @sql_col1 = (@sql_col1 + '->') + CONVERT(nvarchar(50),StepNo) FROM ApproveFlowStep_
select @sql_col1

效果：（图十）

isnull函数有影响，原因暂不明

 

6）Oracle多行合并

函数：wmsys.wm_concat（合并的字段），跟sql server简直是，一个天，一个地，操作太方便了，这里要吐槽一下sql server

原数据：

select * from HRPASSET.V_RESI_SICK_INFO

效果：

 

合并代码：

select wmsys.wm_concat(SICK_ID) as sickIds from HRPASSET.V_RESI_SICK_INFO
where sick_name = '黄专'

 

效果：

 

7）Mysql多行分组合并：通过group_concat函数，然后分组合并，其中默认分割符号：逗号（英文），若需要修改，可以通过separator指定；

例子：

合并sql：

SELECT l.articleId, GROUP_CONCAT(l.labelId) labelIds
FROM bardArticleAndLabel l GROUP BY l.articleId;

效果图：

 

替换分割符号，并通过字段排序sql：

SELECT l.articleId, group_concat(l.labelId order by l.labelId separator '#') labelIds
FROM bardArticleAndLabel l GROUP BY l.articleId;

效果图：

 

参考：oracle 将多个查询结果合并