欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

sqlserver 将多行数据查询合并为一条数据

程序员文章站 2022-05-06 14:40:43
...

有这样一个需求:表T_FUN_TASK为任务表,有字段(TASKID,TASKNAME),表T_FUN_LOGBOOK为日志表,有字段(LOGID,TASKID,LOGDATE),一个任务可持续多天,每天会记录一条日志。在查询表T_FUN_TASK时,需将任务表中的 LOGDATE 查询出来作为一列 LOGDATES显示。

有这样一个需求:表T_FUN_TASK为任务表,有字段(TASKID,TASKNAME),表T_FUN_LOGBOOK为日志表,有字段(LOGID,TASKID,LOGDATE),一个任务可持续多天,每天会记录一条日志。在查询表T_FUN_TASK时,需将任务表中的 LOGDATE 查询出来作为一列 LOGDATES显示。

sqlserver 将多行数据查询合并为一条数据

T_FUN_TASK

sqlserver 将多行数据查询合并为一条数据

T_FUN_LOGBOOK

sqlserver 将多行数据查询合并为一条数据

查询结果

此结果查询方法可以用存储过程轻松实现,这里我要介绍的是sqlserver FOR XML PATH语句的应用,在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据

且合并为一条数据,看以下示例:

SELECT LOGDATE FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH

结果为:


2014-01-06T00:00:00


2014-01-07T00:00:00

首先,将日期格式转化为需要的格式:

SELECT CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH

结果变为:

2014/01/06
2014/01/07

SELECT CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH('')

现在两条结果之间很难区分,需要用下划线将其分隔开来,方法是在CONVERT函数前面加上一个下划线:

SELECT '_'+CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH('')

结果:_2014/01/06_2014/01/07。

在分析了FOR XML PATH语句之后,就来将这个查询结果添加到对T_FUN_TASk的查询结果中去。我的思路是先构建一个子查询,然后查询TASK表时LEFT JOIN这个子查询

SELECT T1.TASKID,'TASKNAME' AS TASKNAME,T2.LOGDATES FROM T_FUN_TASK T1

LEFT JOIN (SELECT TASKID, 
LOGDATES=(SELECT '_'+CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=T1.TASKID FOR XML PATH('')) FROM T_FUN_LOGBOOK T1 
GROUP BY TASKID) T2 ON T2.TASKID=T1.TASKID

ORDER BY T1.TASKID ASC

运行以上SQL后得到的结果为:

sqlserver 将多行数据查询合并为一条数据

发现LOGDATES值的第一个下划线应该去掉,于是修改SQL,应用 STUFF函数去掉第一个下划线:

SELECT T1.TASKID,'TASKNAME' AS TASKNAME,T2.LOGDATES FROM T_FUN_TASK T1

LEFT JOIN (SELECT TASKID, 
LOGDATES=STUFF((SELECT '_'+CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=T1.TASKID FOR XML PATH('')),1,1,'') FROM T_FUN_LOGBOOK T1 
GROUP BY TASKID) T2
ON T2.TASKID=T1.TASKID

ORDER BY T1.TASKID ASC

将结果转化为JSON 返回给前端之后,JS按照下划线分隔这个字段的值,即可以得到一个任务下面日志的填写情况。