sql - 跨表的困难 SQL 连接

Question

我有两个表很容易连接，第三个表给我带来了麻烦。首先是表格:

tbl_customer
id  dt           value  group
a   2013-01-01   10     cavalry
a   2012-06-01   20     lance
a   2011-03-01   10     infantry
b   2013-01-01   20     court
b   2012-07-01   5      maiden
b   2005-06-01   10     chivalry

tbl_title
id  dt_active    dt_inactive   title
a   2001-01-01   2012-01-01    mister
a   2012-01-02   3001-01-01    sir
a   2012-01-02   3001-01-01    king
b   2001-01-01   2012-01-01    miss
b   2012-01-02   3001-01-01    lady
b   2012-01-02   3001-01-01    queen

这很容易加入以返回给定 ID 的标题，使用:

SELECT 
  id, dt, value, title
FROM
  tbl_customer AS cust
INNER JOIN tbl_title AS titles
  ON titles.id = cust.id
  AND dt >= titles.dt_active
  AND dt <= titles.dt_inactive

这将返回 tbl_customer 中的所有行，其中有一些重复行，其中有多个“事件”标题。例如，tbl_customer 中的第一行返回两次，一次是“king”，一次是“sir”作为 title。

我有第三个表将组链接到标题。它可以帮助解决这些关系:

tbl_group
group     title   rank
cavalry   sir     10
lance     king    20
infantry  mister  30
court     lady    10
court     queen   20
maiden    lady    10
chivalry  miss    5

然后我可以连接表格以包含来自 tbl_group 的标题并将结果限制为匹配的结果:

SELECT 
  id, dt, value, titles.title
FROM
  tbl_customer AS cust
INNER JOIN tbl_title AS titles
  ON titles.id = cust.id
  AND dt >= titles.dt_active
  AND dt <= titles.dt_inactive
INNER JOIN tbl_group AS group
  ON group.group = cust.group
WHERE
  titles.title = group.title

这在很大程度上解决了我的问题。当 tbl_title 中有多个“事件”记录时，第三个表有助于消除歧义。

但是，有一个问题。第三个表也可以有多个与组关联的标题。因此，最终输出中仍然可能存在重复。在上面的例子中，“cavalry”明确地与头衔“sir”相关联。但是，“court”组可以是“lady”或“queen”，tbl_title 也没有任何帮助，因为“lady”和“queen”在 tbl_customer.

此时，除了tbl_group中的“rank”，我别无选择。 “Queen”比“lady”高 20 比 10，所以我想在 tbl_customer 中使用该标题作为条目。如果排名相等，我只想使用 tbl_group 中的第一条记录。 (这可以通过某种内部排序来解决吗？)

棘手的部分是在排名之前需要考虑活跃/不活跃的动态。例如，我不想首先为每个组的一个标题过滤 tbl_group，因为这会阻止基于日期的可能匹配。

理想情况下，我需要从显示标题的 tbl_customer 的每个条目返回一行，首先基于事件/非事件日期。接下来，我想使用 tbl_group 消除重复项。最后，我想使用 tbl_group 中的匹配和排名将结果限制为 tbl_customer 中每个条目仅一行。这可能吗？

Answer 1

首先，一些实际的表架构会有所帮助，因为您提供的表数据缺少一些关键元素。每个表中的键是什么？即，什么可以用来唯一标识每个表中的一行？ customer 和 title 表中的 id 列代表什么？当然不是客户标识符，因为有重复项。

其次，要回答您的问题，您可以使用 Row_Number 等排名函数对列表中的项目进行排名，并且只返回给定集合的第一个项目:

With RnkItems As
  (
  Select C.id, C.dt, C.value, T.Title
    , Row_Number() Over ( Partition By C.id, C.dt, C.value, C.[group]
                          Order By G.rank Desc ) As Rnk
  From tbl_customer As C
    Join tbl_title As T
      On T.id = C.id
        And C.dt Between T.dt_active And T.dt_inactive
    Join tbl_group As G
      On G.[group] = C.[group]
        And G.title = T.title
  )
Select id, dt, value, title
From RnkItems
Where Rnk = 1

关键是 Partition By C.id, C.dt, C.value, C.[group] 语句。为什么所有四列？原因是我不知道什么唯一标识客户行。如果该表上有一个主键，我们可以简单地按它进行分区。

SQL Fiddle version