有这样一个需求:表T_FUN_TASK为任务表,有字段(TASKID,TASKNAME),表T_FUN_LOGBOOK为日志表,有字段(LOGID,TASKID,LOGDATE),一个任务可持续多天,每天会记录一条日志。在查询表T_FUN_TASK时,需将任务表中的 LOGDATE 查询出来作为一列 LOGDATES显示。
T_FUN_TASK
T_FUN_LOGBOOK
查询结果
此结果查询方法可以用存储过程轻松实现,这里我要介绍的是sqlserver FOR XML PATH语句的应用,在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据
且合并为一条数据,看以下示例:
SELECT LOGDATE FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH
结果为:
<row> <logdate>2014-01-06T00:00:00</logdate> </row> <row> <logdate>2014-01-07T00:00:00</logdate> </row>
在前面的需求中,我是想要把这2条数据合并到一起且没有XML标签,条与条之间用下划线(_)分隔的,日期的格式也变为了2014/01/07的格式。下面来一步一步来完成需求。
首先,将日期格式转化为需要的格式:
SELECT CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH
结果变为:
<row>2014/01/06</row> <row>2014/01/07</row>
可以发现,转化字符卷的过程中,把logdate标签给去掉了。现在我们要去掉<row>标签:
SELECT CONVERT(VARCHAR(100), LOGDATE, 111) FROM T_FUN_LOGBOOK WHERE TASKID=231 FOR XML PATH('')
在PATH后面将一个空的字符卷作为参数传递,就可以去掉<row>标签,结果:2014/01/062014/01/07。
现在两条结果之间很难区分,需要用下划线将其分隔开来,方法是在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后得到的结果为:
发现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按照下划线分隔这个字段的值,即可以得到一个任务下面日志的填写情况。
原文链接:http://biancheng.dnbcw.net/mssql/467243.html
文章评论