起因 ¶
今天欧阳冰提出一个报表需求，其核心部分可以简化为这样一张表格：
| 调度单号 | 与调度单相关的多张作业单号 |
| 001 | 0001/0002/0003 |
| 002 | 0004 |
| 003 | 0005/0006/0007/0008 |
从上述表格可以看出，如何把调度单的多行作业单明细的单号合并到同一行的一个字段中，并用斜杠 / 分隔，是这个需求的难点。
解决 ¶
于是到网上去搜索了一下，有这样一篇文章：
灵活运用 SQL SERVER FOR XML PATH
其中介绍了 SQL Server 数据库的 FOR XML PATH 关键字的用法。
在一句 SELECT 语句的最后加上
FOR XML PATH
，就可以
把整个结果数据集变成一个 xml 字符串
。大家可以自行尝试一下。
而如果使用
FOR XML PATH('')
，则是
去除所有 xml 节点标记，只剩下原原本本的数据
。我们要的就是这个。
于是，我们可以写 SQL：
SELECT
DH
.
DH_DISPATCH_NO DISPATCH_NO
,
(
SELECT
CLH
.
CLH_CARGO_LOAD_NO
+
'/'
FROM
TM_DISPATCH_CARGO_LOAD_L DCLL
,
TM_CARGO_LOAD_H CLH
WHERE
DH
.
BILLID
=
DCLL
.
BILLID
AND
DCLL
.
DCLL_CARGO_LOAD_HEADER_ID
=
CLH
.
BILLID
FOR
XML PATH
(
''
)
)
NOS
FROM
TM_DISPATCH_H DH
GROUP
BY
DH
.
BILLID
,
DH
.
DH_DISPATCH_NO
;
这样就已经差不多了，但是多张作业单号这一列最后总是多个斜杠 /。
为了把最后这个斜杠去掉，我们还要使用一下 LEFT 函数。那么最终可以使用这样一句 SQL 来满足上述需求：
SELECT
DISPATCH_NO
,
LEFT
(
NOS
,
LEN
(
NOS
)
-
1
)
CARGO_LOAD_NOS
FROM
(
SELECT
DH
.
DH_DISPATCH_NO DISPATCH_NO
,
(
SELECT
CLH
.
CLH_CARGO_LOAD_NO
+
'/'
FROM
TM_DISPATCH_CARGO_LOAD_L DCLL
,
TM_CARGO_LOAD_H CLH
WHERE
DH
.
BILLID
=
DCLL
.
BILLID
AND
DCLL
.
DCLL_CARGO_LOAD_HEADER_ID
=
CLH
.
BILLID
FOR
XML PATH
(
''
)
)
NOS
FROM
TM_DISPATCH_H DH
GROUP
BY
DH
.
BILLID
,
DH
.
DH_DISPATCH_NO
)
TMP
ORDER
BY
DISPATCH_NO
;
