SQL视图开发：获取账户首末次交易日期及借贷类型问题

当前数据结构 #

期望结果 #

现有代码（无法带出借贷类型） #

SELECT * FROM
(
  
  SELECT * FROM
   ( 
     SELECT 'Earliest' as [TransDate], [Account], [Date], [Credit/Debit],
     ROW_NUMBER() OVER (PARTITION BY [Account] ORDER BY [Date]) as rn
     FROM DataTable
   ) e
  WHERE e.rn = 1

  UNION ALL

  SELECT * FROM
   (
     SELECT  'Latest' as [TransDate], [Account], [Date], [Credit/Debit],
     ROW_NUMBER() OVER (PARTITION BY [Account] ORDER BY [Date] DESC) as rn
     FROM DataTable
   ) l
  WHERE l.rn = 1

) t1

PIVOT (min([Date])) FOR [TransDate] in ([Latest], [Earliest])
) P

解决方案 #

方法1：窗口函数实现（简洁高效） #

通过FIRST_VALUE和LAST_VALUE直接提取分组内首尾记录的字段，无需额外聚合：

SELECT DISTINCT
    [账户编号],
    FIRST_VALUE([日期]) OVER (PARTITION BY [账户编号] ORDER BY [日期]) AS 首次交易日期,
    LAST_VALUE([日期]) OVER (PARTITION BY [账户编号] ORDER BY [日期] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 末次交易日期,
    FIRST_VALUE([借贷类型]) OVER (PARTITION BY [账户编号] ORDER BY [日期]) AS 首次借贷类型,
    LAST_VALUE([借贷类型]) OVER (PARTITION BY [账户编号] ORDER BY [日期] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 末次借贷类型
FROM DataTable;

方法2：条件聚合实现（兼容性强） #

先标记每组的首尾记录，再通过条件聚合提取对应字段，逻辑直观：

SELECT
    [账户编号],
    MAX(CASE WHEN rn_asc = 1 THEN [日期] END) AS 首次交易日期,
    MAX(CASE WHEN rn_desc = 1 THEN [日期] END) AS 末次交易日期,
    MAX(CASE WHEN rn_asc = 1 THEN [借贷类型] END) AS 首次借贷类型,
    MAX(CASE WHEN rn_desc = 1 THEN [借贷类型] END) AS 末次借贷类型
FROM (
    SELECT
        [账户编号],
        [日期],
        [借贷类型],
        ROW_NUMBER() OVER (PARTITION BY [账户编号] ORDER BY [日期]) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY [账户编号] ORDER BY [日期] DESC) AS rn_desc
    FROM DataTable
) t
WHERE rn_asc = 1 OR rn_desc = 1
GROUP BY [账户编号];

方法3：调整原PIVOT逻辑 #

原代码仅对日期做转置，需同时处理借贷类型，可通过两次PIVOT后关联实现：

SELECT
    p.Account,
    p.Earliest AS 首次交易日期,
    p.Latest AS 末次交易日期,
    pd.Earliest_CreditDebit AS 首次借贷类型,
    pd.Latest_CreditDebit AS 末次借贷类型
FROM (
    SELECT * FROM
    (
        SELECT 'Earliest' as TransDate, Account, Date
        FROM (
            SELECT Account, Date,
                   ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date) as rn
            FROM DataTable
        ) e WHERE rn=1
        UNION ALL
        SELECT 'Latest' as TransDate, Account, Date
        FROM (
            SELECT Account, Date,
                   ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date DESC) as rn
            FROM DataTable
        ) l WHERE rn=1
    ) t1
    PIVOT (MIN(Date) FOR TransDate IN ([Latest], [Earliest])) p
) p
JOIN (
    SELECT * FROM
    (
        SELECT 'Earliest_CreditDebit' as TransType, Account, [Credit/Debit]
        FROM (
            SELECT Account, [Credit/Debit],
                   ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date) as rn
            FROM DataTable
        ) e WHERE rn=1
        UNION ALL
        SELECT 'Latest_CreditDebit' as TransType, Account, [Credit/Debit]
        FROM (
            SELECT Account, [Credit/Debit],
                   ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date DESC) as rn
            FROM DataTable
        ) l WHERE rn=1
    ) t2
    PIVOT (MIN([Credit/Debit]) FOR TransType IN ([Latest_CreditDebit], [Earliest_CreditDebit])) pd
) pd ON p.Account = pd.Account;

内容的提问来源于stack exchange，提问作者Tim Guerin