sqlserver查询一对多的关系、合并多条记录的某字段值到一个字段
需求场景
现有如下两个表格construct和attachment，通过construct.id = attachment.link_id进行关联。
- 表 construct
| id | type | state | title | detail |
|---|---|---|---|---|
| 1 | 房地产 | 正在建设 | 汉峪金谷1 | 汉峪金谷1建设详情 |
| 2 | 房地产 | 正在建设 | 汉峪金谷2 | 汉峪金谷2建设详情 |
| 3 | 房地产 | 正在建设 | 汉峪金谷3 | 汉峪金谷3建设详情 |
| 4 | 房地产 | 正在建设 | 汉峪金谷4 | 汉峪金谷4建设详情 |
| 5 | 房地产 | 正在建设 | 汉峪金谷5 | 汉峪金谷5建设详情 |
| 6 | 房地产 | 正在建设 | 汉峪金谷6 | 汉峪金谷6建设详情 |
| 7 | 房地产 | 正在建设 | 汉峪金谷7 | 汉峪金谷7建设详情 |
| 8 | 房地产 | 正在建设 | 汉峪金谷8 | 汉峪金谷8建设详情 |
| 9 | 房地产 | 正在建设 | 汉峪金谷9 | 汉峪金谷9建设详情 |
| 10 | 房地产 | 正在建设 | 汉峪金谷10 | 汉峪金谷10建设详情 |
| 11 | 房地产 | 正在建设 | 汉峪金谷11 | 汉峪金谷11建设详情 |
| 12 | 房地产 | 正在建设 | 汉峪金谷12 | 汉峪金谷12建设详情 |
| 13 | 房地产 | 正在建设 | 汉峪金谷13 | 汉峪金谷13建设详情 |
| 18 | 房地产 | 正在建设 | 汉峪金谷14 | 汉峪金谷14建设详情 |
| 19 | 城市交通 | 规划中 | 经十路绿化改造 | 经十路绿化改造建设详情 |
| 20 | 房地产 | 正在建设 | 汉峪金谷小学 | 汉峪金谷小学建设详情 |
| 21 | 房地产 | 正在建设 | 万达 | 万达建设详情 |
- 表attachment
| id | file_type | file_name | file_path | link_id |
|---|---|---|---|---|
| 1 | 房地产 | NULL | UploadFile\房地产\20190521094020_488565885.png | NULL |
| 2 | 房地产 | NULL | UploadFile\房地产\20190521094020_488565888.png | NULL |
| 3 | 房地产 | NULL | UploadFile\房地产\20190521094020_401360686.png | NULL |
| 4 | 房地产 | NULL | UploadFile\房地产\tmp_e08784ed754d0261517d218d7caee08293fed9c0ebcf53e8.jpg | 11 |
| 5 | 房地产 | NULL | UploadFile\房地产\20190521095050tmp_e08784ed754d0261517d218d7caee08293fed9c0ebcf53e8.jpg | 12 |
| 6 | 城市交通 | NULL | UploadFile\城市交通\20190521140341_52478786.png | 19 |
| 7 | 城市交通 | NULL | UploadFile\城市交通\20190521140341_52478788.png | 19 |
| 8 | 城市交通 | NULL | UploadFile\城市交通\20190521140341_52478786.png | 19 |
| 9 | 房地产 | NULL | UploadFile\房地产\20190522094023_813675778.png | 20 |
| 10 | 房地产 | NULL | UploadFile\房地产\20190522094023_813675776.png | 20 |
| 11 | 房地产 | NULL | UploadFile\房地产\tmp_a0b9403f7cf36457b661ac0f446f61d39bc3d201b35db080.jpg | 21 |
| 12 | 房地产 | NULL | UploadFile\房地产\tmp_b1e1ac79eaf26cf78d653c582fa06610d20f9ebbd378798a.jpg | 21 |
现需要从attachment表提取每一个construct对应的多个file_path值，并与construct合并。
方法
通过stuff()、 for xml path() 进行拼接，直接上sql语句：
select c.*,
STUFF( (select file_path+',' from attachment where link_id=c.id for xml path('')),
1, 0, '' ) files
from construct c
查询结果为：
| id | type | state | title | detail | files |
|---|---|---|---|---|---|
| 1 | 房地产 | 正在建设 | 汉峪金谷1 | 汉峪金谷1建设详情 | NULL |
| 2 | 房地产 | 正在建设 | 汉峪金谷2 | 汉峪金谷2建设详情 | NULL |
| 3 | 房地产 | 正在建设 | 汉峪金谷3 | 汉峪金谷3建设详情 | NULL |
| 4 | 房地产 | 正在建设 | 汉峪金谷4 | 汉峪金谷4建设详情 | NULL |
| 5 | 房地产 | 正在建设 | 汉峪金谷5 | 汉峪金谷5建设详情 | NULL |
| 6 | 房地产 | 正在建设 | 汉峪金谷6 | 汉峪金谷6建设详情 | NULL |
| 7 | 房地产 | 正在建设 | 汉峪金谷7 | 汉峪金谷7建设详情 | NULL |
| 8 | 房地产 | 正在建设 | 汉峪金谷8 | 汉峪金谷8建设详情 | NULL |
| 9 | 房地产 | 正在建设 | 汉峪金谷9 | 汉峪金谷9建设详情 | NULL |
| 10 | 房地产 | 正在建设 | 汉峪金谷10 | 汉峪金谷10建设详情 | NULL |
| 11 | 房地产 | 正在建设 | 汉峪金谷11 | 汉峪金谷11建设详情 | UploadFile\房地产\tmp_e08784ed754d0261517d218d7caee08293fed9c0ebcf53e8.jpg, |
| 12 | 房地产 | 正在建设 | 汉峪金谷12 | 汉峪金谷12建设详情 | UploadFile\房地产\20190521095050tmp_e08784ed754d0261517d218d7caee08293fed9c0ebcf53e8.jpg, |
| 13 | 房地产 | 正在建设 | 汉峪金谷13 | 汉峪金谷13建设详情 | NULL |
| 18 | 房地产 | 正在建设 | 汉峪金谷14 | 汉峪金谷14建设详情 | NULL |
| 19 | 城市交通 | 规划中 | 经十路绿化改造 | 经十路绿化改造建设详情 | UploadFile\城市交通\20190521140341_52478786.png,UploadFile\城市交通\20190521140341_52478788.png,UploadFile\城市交通\20190521140341_52478786.png, |
| 20 | 房地产 | 正在建设 | 汉峪金谷小学 | 汉峪金谷小学建设详情 | UploadFile\房地产\20190522094023_813675778.png,UploadFile\房地产\20190522094023_813675776.png, |
| 21 | 房地产 | 正在建设 | 万达 | 万达建设详情 | UploadFile\房地产\tmp_a0b9403f7cf36457b661ac0f446f61d39bc3d201b35db080.jpg,UploadFile\房地产\tmp_b1e1ac79eaf26cf78d653c582fa06610d20f9ebbd378798a.jpg, |
更多文章请移步我的博客：
都不敢自称码农的Coder
网友评论