sql-server - 在 SQL Server 中跨多行分别连接多列
问题描述
我需要在两个表之间连接的 SQL Server 中的多行数据中分别连接两列。
例如,我有一个“关联”表和“市场”表,如下所示“
Association:
user_id | role_id | market_id
-------------------------------
1 | a | 1
1 | a | 2
2 | c | 3
2 | c | 4
2 | c | 5
Market:
market_id | market_name
-----------------------
1 | Arizona
2 | Utah
3 | Indiana
4 | Illinois
5 | Kentucky
我需要将 Association 表中的 market_id 连接成一个值,每个用户 id,并从 Market 表中引入连接的市场名称,我想要的输出如下所示:
user_id | role_id | market_ids | market_names
--------------------------------------------------
1 | a | 1,2 | Arizona,Utah
2 | c | 3,4,5 | Indiana,Illinois,Kentucky
现在,我能够成功地连接市场 id,但仍然使用以下 SQL 为每个市场提供多行数据:
SELECT DISTINCT
a.user_id,
a.role_id,
SUBSTRING(
(
SELECT DISTINCT ', ' + market_id AS [text()]
FROM Association aa
WHERE a.user_id = aa.user_id
FOR XML PATH ('')
), 2, 1000) [market_ids],
SUBSTRING(
(
SELECT DISTINCT ', ' + market_name AS [text()]
FROM Market bb
WHERE a.market_id = bb.market_id
FOR XML PATH ('')
), 2, 1000) [Market Name]
FROM
Association a
INNER JOIN
Market b
ON a.market_id = d.market_id
关于如何实现第二个连接的任何想法?
解决方案
使用 Row_Number() 来保持演示顺序的 CTE 应该可以解决问题
例子
;with cte as (
Select A.*
,B.Market_Name
,RN = Row_Number() over (Partition By user_id,role_id order by a.market_id)
From Association A
Join Market B on A.market_id=B.market_id
)
Select Distinct
user_id
,role_id
,market_ids = stuff((Select concat(',',market_id ) From cte Where user_id=A.user_id and role_id=A.role_id Order By RN For XML Path ('')),1,1,'')
,market_names = stuff((Select concat(',',market_name) From cte Where user_id=A.user_id and role_id=A.role_id Order By RN For XML Path ('')),1,1,'')
From cte A
退货
user_id role_id market_ids market_names
1 a 1,2 Arizona,Utah
2 c 3,4,5 Indiana,Illinois,Kentucky
推荐阅读
- javascript - 自动完成在自动完成窗口中显示相关数据
- symfony - symfony 3.4 中的 Flash 消息
- scala - 虽然解析注释在枚举中找不到 MAYBE
. 斯卡拉 + 千分尺 - c++ - C++ 项目中 C++/CLI dll 的未解决符号错误
- angular - Spotify Embed iFrame 仅播放歌曲的预览
- influxdb - 通过 Influx HTTP API 写入数据返回 OK,但表为空
- r - 如何更改 ggplot2 geom_raster 中的插值/平滑
- c++ - Qt 驱动器下拉列表仅 USB 棒
- html - Groovy/Grails Hibernate 无法创建 bean 事务
- php - php 致命错误类Illumination\console\command not found