TSQL -不使用游标将一个表映射到另一个表

Question

我有如下结构的表

create table Doc(
    id                  int identity(1, 1) primary key,
    DocumentStartValue  varchar(100)
)
create Metadata (
    DocumentValue       varchar(100),
    StartDesignation    char(1),
    PageNumber          int
)
GO

Doc contains
    id      DocumentStartValue
    1000    ID-1
    1100    ID-5
    2000    ID-8
    3000    ID-9

Metadata contains
    Documentvalue   StartDesignation    PageNumber
    ID-1            D                   0
    ID-2            NULL                1
    ID-3            NULL                2
    ID-4            NULL                3
    ID-5            D                   0
    ID-6            NULL                1
    ID-7            NULL                2
    ID-8            D                   0
    ID-9            D                   0

我需要做的是将Metadata.DocumentValues映射到Doc.id

所以我需要的结果是

id      DocumentValue   PageNumber
1000    ID-1            0
1000    ID-2            1
1000    ID-3            2
1000    ID-4            3
1100    ID-5            0
1100    ID-6            1
1100    ID-7            2
2000    ID-8            0
3000    ID-9            0

不使用游标也能实现吗？

Answer 1

像这样的话，抱歉不能测试

;WITH RowList AS
(   --assign RowNums to each row...
    SELECT
        ROW_NUMBER() OVER (ORDER BY id) AS RowNum,
        id, DocumentStartValue
    FROM
        doc
), RowPairs AS
(   --this allows us to pair a row with the previous rows to create ranges
    SELECT 
       R.DocumentStartValue AS Start, R.id,
       R1.DocumentStartValue AS End
    FROM
       RowList R JOIN RowList R1 ON R.RowNum + 1 = R1.RowNum
)
--use ranges to join back and get the data
SELECT
    RP.id, M.DocumentValue, M.PageNumber
FROM
    RowPairs RP
    JOIN
    Metadata M ON RP.Start <= M.DocumentValue AND M.DocumentValue < RP.End

编辑:这假设您可以依赖ID-x值匹配并且是升序的。如果是，则StartDesignation是多余的/冗余的，并且可能与单据表DocumentStartValue冲突

Answer 2

with rm as
(
  select DocumentValue
    ,PageNumber
    ,case when StartDesignation = 'D' then 1 else 0 end as IsStart
    ,row_number() over (order by DocumentValue) as RowNumber
  from Metadata
)
,gm as
(
  select
     DocumentValue as DocumentGroup
    ,DocumentValue
    ,PageNumber
    ,RowNumber
  from rm
  where RowNumber = 1

  union all

  select
     case when rm.IsStart = 1 then rm.DocumentValue else gm.DocumentGroup end
    ,rm.DocumentValue
    ,rm.PageNumber
    ,rm.RowNumber
  from gm
  inner join rm on rm.RowNumber = (gm.RowNumber + 1)
)
select d.id, gm.DocumentValue, gm.PageNumber
from Doc d
inner join gm on d.DocumentStartValue = gm.DocumentGroup

尝试使用上面的查询(可能还需要添加option (maxrecursion ...) )，并在元数据表的DocumentValue上添加索引。此外，如果可能的话，最好在插入Metadat行时保存适当的组。

更新：我已经对它进行了测试，并修复了我的查询中的错误，但它不能像最初的问题那样工作并给出结果。

UPD2：和推荐的索引：

create clustered index IX_Metadata on Metadata (DocumentValue)
create nonclustered index IX_Doc_StartValue on Doc (DocumentStartValue)