我有一个表A,B和C有很多列(30+)。主要列均为Id、RefNumber
我也有表LinkedEntity，我可以匹配记录从不同的表(A,B或C)
我需要从表A中选择所有记录，并从B和C中显示链接记录
| Id | RefNumber | OtherColumns | 101 | A101 | … |
|---|---|---|
| 102 | A102 | … |
这是一个使用连接的简单方法，我在LinkedEntity中测试了一些额外的重复条目(您当前的设计允许这样做，您可以使用EntityId和LinkedEntityId的复合键并从此表中删除Id来修复此问题)。
对于旧版本的sql server，您确实需要使用STRING_AGG或其他方法之一来分组您的记录。此外，如果您对分组连接的数据排序感兴趣，文档提供了如何排序数据的其他示例。
使用IN操作符和左连接来检索链接实体，使用distinct关键字来删除重复项。
查询1
SELECT
AId as Id,
ARefNumber as RefNumber,
STRING_AGG(BId,',') as LinkedB,
STRING_AGG(BRefNumber,',') as LinkedBRefNumb,
STRING_AGG(CId,',') as LinkedC,
STRING_AGG(CRefNumber,',') as LinkedCRefNumb
FROM (
SELECT DISTINCT
A.Id as AId,
A.RefNumber as ARefNumber,
B.Id as BId,
B.RefNumber as BRefNumber,
C.Id as CId,
C.RefNumber as CRefNumber
FROM
A
LEFT JOIN
LinkedEntity le ON A.Id IN (le.EntityId,le.LinkedEntityId)
LEFT JOIN
B ON B.Id IN (le.EntityId,le.LinkedEntityId)
LEFT JOIN
C ON C.Id IN (le.EntityId,le.LinkedEntityId)
) t
GROUP BY
AId,
ARefNumber
LinkedCRefNumbB102 空 C101
您是否尝试过外键(对于我所理解的，我不确定表是否由id或refnumber链接):
ALTER TABLE A
ADD CONSTRAINT Foreign1 FOREIGN KEY (Id)
REFERENCES B (Id)
ON DELETE RESTRICT
ON DELETE RESTRICT
等等，从b到c，从Linkedentity到a, b, c。这里是微软官方页面https://learn.microsoft.com/it-it/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15
那么你所要做的就是:
SELECT * FROM A JOIN B JOIN C JOIN LinkedEntity
SELECT
a_id,
a_RefNumber,
GROUP_AGGR(b_id) AS b_aggr_id,
GROUP_AGGR(b_RefNumber) AS b_aggr_ref,
GROUP_AGGR(c_id) as c_aggr,
GROUP_AGGR(c_RefNumber) AS c_aggr_ref
FROM (
SELECT
A.id AS a_id,
A.RefNumber AS a_RefNumber,
B.id AS b_id,
B.RefNumber AS b_RefNumber,
NULL AS c_id,
NULL AS c_RefNumber
FROM A
LEFT JOIN LinkedEntity X ON X.EntityId=A.id
LEFT JOIN B ON X.LinkedEntityId=B.id
UNION ALL
SELECT
A.id AS a_id,
A.RefNumber AS a_RefNumber,
NULL AS b_id,
NULL AS b_RefNumber,
C.id AS c_id,
C.RefNumber AS c_RefNumber
FROM A
LEFT JOIN LinkedEntity X ON X.EntityId=A.id
LEFT JOIN C ON X.LinkedEntityId=C.id
) AS matrix
GROUP BY a_id, a_RefNumber
ORDER BY 1