代码之家 › 专栏 › 技术社区 › demo

使用特定逻辑选择不重复的记录

sql-server sql

demo · 技术社区 · 4 年前

我有一张桌子 A , B 和 C 有很多列(30+)。所有的主列都是 Id , RefNumber

我还有桌子 LinkedEntity 在那里我可以匹配不同表中的记录( A. , B 或 C )

我需要从表中选择所有记录 A. 并显示来自 B 和 C

身份证件	参考号	其他栏目
101	A101	...
102	A102	...

身份证件	参考号	其他栏目
201	B101	...
202	B102	...

身份证件	参考号	其他栏目
301	C101	...
302	C102	...

亲缘关系

身份证件	实体ID	LinkedEntityId
1.	101	202
2.	102	301
3.	102	201
4.	102	202

预期结果:

身份证件	参考号	LinkedB	LinkedBRefNumb	LinkedC	LinkedRefNumb
101	A101	202	B102	无效的	无效的
102	A102	201,202	B101,B102	301	C101

第一个想法是写

SELECT A.Id, A.RefNumber, L1.Id, L1.RefNumber, L2.Id, L2.RefNumber
FROM A
LEFT JOIN (SELECT B.Id, B.RefNumber, le.EntityId, le.LinkedEntityId FROM B JOIN LinkedEntity le ON le.EntityId = B.Id OR le.LinkedEntityId = B.Id) L1
ON A.Id = L1.EntityId OR A.Id = L1.LinkedEntityId 
LEFT JOIN (SELECT C.Id, C.RefNumber, le.EntityId, le.LinkedEntityId FROM C JOIN LinkedEntity le ON le.EntityId = C.Id OR le.LinkedEntityId = C.Id) L2
ON A.Id = L2.EntityId OR A.Id = L2.LinkedEntityId

但此查询返回的记录重复 A. 桌子有没有办法删除重复项并加入LinkedEntity的值?(可能用 STRING_AGG ) ?

0 回复 | 直到 4 年前

ggordon 4 年前

下面是一种使用联接的简单方法,我在LinkedEntity中使用了一些额外的重复条目对其进行了测试(您当前的设计允许这样做,您可以使用EntityId和LinkedEntityId的复合键修复此问题,并从此表中删除该Id)。

您确实需要使用 STRING_AGG 或 one of these other approaches 对于较旧版本的sql server。此外,如果您有兴趣对分组的连接数据进行排序,则 docs 提供如何订购数据的其他示例。

这使用 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

身份证件	参考号	LinkedB	LinkedBRefNumb	LinkedC	LinkedRefNumb
101	A101	202	B102	无效的	无效的
102	A102	201,202	B101,B102	301	C101

View working demo db fiddle

让我知道这是否适合你。

-1

Alessandro 4 年前

您是否尝试过外键(据我所知,我不确定表是否通过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,依此类推。这里是ms官方页面 https://docs.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

-1

verhie 4 年前

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