sql - 一对一左连接

Question

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

(1) 最合适的连接是每个表中的类别和价格都匹配。这是第 2 类的情况(注意表 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');

Answer 1

听起来使用两个左连接就可以了:

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
 ...