sql-server - 使用表值函数以列而不是行的形式返回数据

Question

我正在尝试编写一个查询，它将采用有限数量的历史记录并将结果显示在一行中。

比如我有一张人表:

|PersonID|Forename|Surname
|--------|--------|----------
|00000001|Andy    |Cairns
|00000002|John    |Smith

以及所有历史地址的表格:

|PersonID|Date      |Street       |Town
-------------------------------------------
|00000001|2011-01-01|Main Street  |MyTown
|00000001|2010-01-01|Old Street   |OldTown
|00000002|2010-01-01|Diagon Alley |London
|00000001|2009-01-01|First Street |OtherTown

等..

我想返回以下内容:

|PersonID|Name  |MoveDate1 |Town1 |MoveDate2 |Town2  |MoveDate3 |Town3
------------------------------------------------------------------------
|00000001|Andy  |2011-01-01|MyTown|2010-01-01|OldTown|2009-01-01|OtherTown
|00000002|John  |2010-01-01|London|          |       |          |

目前，我正在使用以下查询:

select PersonID, Name, s.mdate, s.town
from dbo.people
cross apply dbo.getAddressList as s

和下面的表值函数:

alter function [dbo].[getAddressList]
(
 @personID
)
returns
 @addresslisttable
(
  mdate smalldatetime
  town char
)
as
begin
  insert into @addresslist (
    mdate
    town
  )
  select top 3 mdate, town
  from dbo.addresses
      where PersonID = @personID
      order by mdate desc
return
end

不幸的是，这会为每个地址返回一个新行，如下所示:

|PersonID|Name|MDate     |Town
|00000001|Andy|2011-01-01|MyTown
|00000001|Andy|2010-01-01|OldTown
|00000001|Andy|2009-01-01|OtherTown

如何改为返回字段中的每个返回行？

提前致谢。

Answer 1

在可能的情况下，您应该始终优先使用内联 TVF 而不是多语句 TVF。

ALTER FUNCTION [dbo].[getAddressList] 
(   
@personID INT
)
RETURNS TABLE 
AS
RETURN 
(
WITH cte AS
      (SELECT TOP 3 mdate, town, ROW_NUMBER() OVER (ORDER BY mdate DESC) rn
      FROM dbo.addresses
          WHERE PersonID = @personID
          ORDER BY mdate DESC
        )

    SELECT
    MAX(CASE WHEN rn=1 THEN mdate END) AS MoveDate1,
    MAX(CASE WHEN rn=1 THEN town END) AS Town1,
    MAX(CASE WHEN rn=2 THEN mdate END) AS MoveDate2,
    MAX(CASE WHEN rn=2 THEN town END) AS Town2,
    MAX(CASE WHEN rn=3 THEN mdate END) AS MoveDate3,
    MAX(CASE WHEN rn=3 THEN town END) AS Town3
    FROM cte
)

我还会研究完全不使用 TVF 的相对性能。并执行 JOIN、ROW_NUMBER() OVER (PARTITION BY PersonID) 和上面的 PIVOT 技术。