对于表中的每一行，获取另一个表中的任何一个链接行

Given the following (heavily simplified) tables:

鉴于以下（大大简化）表：

create table Tags (
   TagId int Primary Key
)

create table OrderLines (
   Branch int,
   Station int,
   TransNo int,
   TagId int foreign key references Tags,
   primary key (Branch, Station, TransNo)
)

I need a list of Tags along with an OrderLine which references each Tag. I am expecting zero or one OrderLines to reference each Tag, but there is nothing in the database constraints to enforce this.

我需要一个标签列表以及一个引用每个标签的OrderLine。我期望零或一个OrderLines引用每个Tag，但数据库约束中没有任何内容可以强制执行此操作。

Given input like this:

给出这样的输入：

 OrderLines                                     Tags
 Branch Station TransNo TagId                   TagId
 1      100     2345    1                       1
 1      100     2346    1                       2
 1      101     5223    2                       3
 3      100     6677    4                       4

I want to get output like this:

我想获得这样的输出：

  TagId     Branch    Station    TransNo  
  1         1         100        2345     <-- it could list 2346 here, don't care
  2         1         101        5223     
  3         null      null       null
  4         3         100        6677

Note that although TagId 1 is referenced twice, my output only contains one of it. It doesn't matter which OrderLine is listed alongside it, but there must be only one instance of each tag in the output.

请注意，尽管TagId 1被引用了两次，但我的输出只包含其中一个。列出哪个OrderLine并不重要，但输出中每个标签必须只有一个实例。

What is the most efficient way to do this?

最有效的方法是什么？

I'm not able to alter the database schema.

我无法改变数据库架构。

4 个解决方案

#1

You'd have to look at the execution plan to gauge efficiency

您必须查看执行计划以衡量效率

;WITH O AS
(
SELECT Branch, Station, TransNo, TagId,
ROW_NUMBER() OVER (PARTITION BY TagId ORDER BY TagId ) AS RN
FROM OrderLines
)
    SELECT T.TagID, O.Branch, O.Station, O.TransNo
        FROM Tags T
        LEFT JOIN O ON T.TagID = O.TagID and RN=1

#2

select t.TagId, t.Station, t.Branch, t.TransNo
from (
SELECT Station, Branch, TransNo, TagId, ROW_NUMBER() OVER (partition by TagId order by TagId) r
From OrderLines) t
WHERE r = 1
UNION ALL
SELECT TagId, NULL, NULL, NULL
from Tags
WHERE NOT EXISTS (Select 1 from OrderLines ol Where ol.TagId = Tags.Id)

#3

SELECT Tags.TagID, Branch, Station, TransNo
    FROM Tags
    LEFT JOIN OrderLines ON Tags.TagID = OrderLines.TagID
    ORDER BY Tags.TagID

The left join will make sure that all tags get listed, even those that don't have any order line attached to them. The only flaw is that if more than one order line references a tag, the tag will be listed once for each order line. Some DBMS's (e.g. MySQL) allow you to solve this using a GROUP BY and still select columns you don't group by, but it's nonstandard and there is no guarantee which OrderLine you'll get. If you want this, you'll have to resort to subqueries, unions, temporary tables, or views (but since you can't change the schema, that last option is out).

左连接将确保列出所有标签，即使那些没有连接任何订单行的标签也是如此。唯一的缺陷是，如果多个订单行引用标签，则每个订单行将标记一次标签。某些DBMS（例如MySQL）允许您使用GROUP BY解决此问题并仍然选择不分组的列，但它是非标准的，并且无法保证您将获得哪个OrderLine。如果你想要这个，你将不得不求助于子查询，联合，临时表或视图（但由于你不能改变架构，最后一个选项就出来了）。

#4

   select
        T.tagid,
        O.branch,
        O.station,
        O.transno
    from orderlines O
    right join tags T on (t.tagid=O.tagid)
    group by t.tagid

Result:

结果：

  tagid branch  station transno
1   1   100 2345
2   1   101 5223
3   NULL    NULL    NULL
4   3   100 6677