sql - 一对一左连接

如何在每个类别中仅使用一对一行进行左连接。这是类别 ID 和产品价格。请注意，如果我使用 LEFT JOIN，我不希望在第 5 类中出现重复。



(1) 最合适的联接是当每个表中的类别和价格都匹配时。这是第二类的情况(注意表A和B中的行顺序不同)
(2) 如果只有类别匹配，那么我想显示任何行的字段(就像我在第一个类别中所做的那样，它是许多行之一)。
(3)如果类别和价格都不匹配，我想得到NULL。

我使用了以下查询，但对我来说太慢了。

with
A as (select A.id, A.price
,ROW_NUMBER() over(partition BY id) as Row_id_A
,ROW_NUMBER() OVER(PARTITION BY id, price order by price asc) AS [Row_id_price_A]
from TableA as A)
,
B as (select B.id, B.price, B.field
,ROW_NUMBER() over(partition BY id) as Row_id_B
,ROW_NUMBER() OVER(PARTITION BY id, price order by price asc) AS [Row_id_price_B]
from TableB as B)

select A.id, A.price, A.Row_A,
,ResultField=case
    when A.Row_id_A=C.Row_id_B then C.field
    when [Row_id_price_A]=[Row_id_price_B] then D.field
    else N'One of many: '+C.field
    end
from A

outer apply (select top 1 * from B
where
A.id=B.id
and Row_A=Row_B
) as C

outer apply (select top 1 * from B
where
A.id=B.id
and Row_A=Row_B
and [Row_id_price_A]=[Row_id_price_B]
) as D

更新。我添加示例数据:

CREATE TABLE dbo.TableA(
   id    INTEGER NOT NULL
  ,price INTEGER NOT NULL
);
INSERT INTO TableA(id,price) VALUES (1,50);
INSERT INTO TableA(id,price) VALUES (2,20);
INSERT INTO TableA(id,price) VALUES (2,30);
INSERT INTO TableA(id,price) VALUES (2,50);
INSERT INTO TableA(id,price) VALUES (4,15);
INSERT INTO TableA(id,price) VALUES (4,5);
INSERT INTO TableA(id,price) VALUES (5,100);
INSERT INTO TableA(id,price) VALUES (5,100);

CREATE TABLE dbo.TableB(
   id    INTEGER NOT NULL
  ,price INTEGER NOT NULL
  ,field VARCHAR(2) NOT NULL
);
INSERT INTO TableB(id,price,field) VALUES (1,1,'A1');
INSERT INTO TableB(id,price,field) VALUES (2,30,'A2');
INSERT INTO TableB(id,price,field) VALUES (2,50,'A3');
INSERT INTO TableB(id,price,field) VALUES (2,20,'A4');
INSERT INTO TableB(id,price,field) VALUES (5,5,'A5');
INSERT INTO TableB(id,price,field) VALUES (5,100,'A6');
INSERT INTO TableB(id,price,field) VALUES (5,100,'A7');
INSERT INTO TableB(id,price,field) VALUES (6,1,'A8');

最佳答案

听起来像使用两个左连接会工作得很好:

select
 ...
 coalesce (B1.Field, B2.Field) as Field,
 ...

left join TableB B1 on B1.id = TableA.id and B1.price = TableA.price
left join TableB B2 on B2.id = TableA.id

这通常会很棘手，因为它可能会给您带来行重复的麻烦，但在您的情况下不应该受到伤害。

如果您还需要 One of many 文本，只需将其添加到合并中，例如coalesce(B1.Field, 'One of many: ' + B2.Field) - 不过，请确保您拥有正确的类型。

编辑:

哦，你确实关心重复。在这种情况下，子查询可能是更好的选择:

select
 ...
 coalesce(B1.Field, (select top 1 Field from TableB where id = TableA.id)) as Field
 ...

关于sql - 一对一左连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32067555/