时间戳

首页 > 解决方案 > 如何关联不直接关联的列？

sql - 如何关联不直接关联的列？

问题描述

我有这个数据：

label    ui     dc          mapId

1        209459 50580-590   1      
1        198440 50580-590   2      

2        209459 50580-449   3      
2        198440 50580-449   4      

3        198440 43353-066   5      
3        198440 43353-067   6      
3        198440 43353-059   7           

4        187878 89999-876   8

label我使用该列将其插入并映射到该查询中：

DECLARE @dc TABLE (label int, dc varchar(50))
DECLARE @ui TABLE (label int, ui varchar(50))

INSERT INTO @DC
VALUES (1, '50580-590'), (2, '50580-449'), (3, '43353-066'), (3, '43353-067'), (3, '43354-059'), (4, '89999-876')

INSERT INTO @UI
VALUES (1, '209459'), (1, '198440'), (2, '209459'), (2, '198440'), (3, '198440'), (4, '187878')

SELECT *
FROM @dc A
INNER JOIN (    
    SELECT *
    FROM @ui
    ) B
ON A.label = B.label

我试图找到以某种方式相互关联的行。例如，我想查找 where 的行dc = 43353-059以及与该行关联的 ui，这将是 where 的行mapId = 7。在该行中ui = 198440，在我这样做之后，我想查找 ui 为 198440 的行，但随后我想查看标签。如果取 row where ，带有has的mapId = 2ui也关联到. 这样你就可以说是相关的。使用该逻辑，我尝试使用顶部的数据进行查询并获得以下结果：198440label of 1 but on mapId 1 ui of 209459label of 1mapId 1 - 7

label    ui     dc          masterId

1        209459 50580-590   1
1        198440 50580-590   2

3        209459 50580-449   3
3        198440 50580-449   4

3        198440 43353-066   5
3        198440 43353-067   6
3        198440 43353-059   7

4        187878 89999-876   1

由于我无法以任何方式将 mapId 8 与其他行相关联，因此我从 masterId 列上的 1 开始。任何帮助表示赞赏。

标签： sqlsql-server

解决方案

请看一看

DECLARE @Crossings TABLE (Label INT,Value INT)
INSERT INTO @Crossings(label,[Value])
SELECT DISTINCT up.Label,up.Value
FROM (
    SELECT dc.label AS [Label],dc.RelatedLabel AS [1],ui.label AS [2],ui.RelatedLabel AS [3]
    FROM (
        SELECT dc.label,dc2.label AS [RelatedLabel]
        FROM @dc dc
        LEFT JOIN @dc dc2 ON dc2.dc = dc.dc
    ) dc
    INNER JOIN (
        SELECT ui.label,ui2.label AS [RelatedLabel]
        FROM @ui ui
        LEFT JOIN @ui ui2 On ui2.ui = ui.ui
    ) ui ON ui.label = dc.label OR ui.label = dc.RelatedLabel OR ui.RelatedLabel = dc.RelatedLabel
) a
UNPIVOT(Value FOR Param IN ([1],[2],[3])) up
ORDER BY up.label
;

DECLARE @Group TABLE (Label INT,TheGroup NVARCHAR(255))
;WITH cte AS (
    SELECT DISTINCT g.label,g2.label AS [RelatedLabel]
    FROM @Crossings g
    LEFT JOIN @Crossings g2 ON (g2.[Value] = g.[label] OR g2.[label] = g.[Value])
)
INSERT INTO @Group(Label,TheGroup)
SELECT DISTINCT d.Label
    ,STUFF((SELECT DISTINCT ',' + CONVERT(NVARCHAR(50),r.RelatedLabel) FROM cte r WHERE r.Label = d.Label ORDER BY 1 FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS [TheGroup]
FROM cte d
;


SELECT dc.label,dc.dc,ui.ui
    ,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS [MapId]
    ,ROW_NUMBER()OVER(PARTITION BY g.TheGroup ORDER BY dc.label) AS [MasterId]
FROM @dc dc
INNER JOIN @ui ui ON ui.label = dc.label
INNER JOIN @Group g ON g.Label = dc.label
;

推荐阅读