对第二个最老结果执行此递归查询的更好方法

Question

这很好，但我从其他所以贴复制和粘贴了段，如代码注释中所述。有更好的方法吗？

SQL Fiddle

MS Server 2008架构设置

CREATE TABLE GoalType ([GoalTypeID] int, [Type] varchar(25));
INSERT INTO GoalType ([GoalTypeID], [Type]) VALUES 
    (1, 'Price'),
    (2, 'Revenue'),
    (3, 'Type2 Revenue'),
    (4, 'Graduation Percentage'),
    (5, 'Retention Percentage')
;

CREATE TABLE Goal ([GoalID] int, [GoalTypeID] int, [GymID] int, [Month] int, [Year] int, [Goal] int, [Created] datetime, [CreatedBy] int);
INSERT INTO Goal ([GoalID], [GoalTypeID], [GymID], [Month], [Year], [Goal], [Created], [CreatedBy]) VALUES
    (1, 1, 8, 10, 2014, 3500, '2014-10-14 11:09:03', 12345),
    (2, 1, 8, 10, 2014, 2500, '2014-10-14 11:09:04', 12345),
    (3, 1, 8, 10, 2014, 1500, '2014-10-14 11:09:05', 12345),
    (4, 1, 8, 9, 2014, 3500, '2014-10-14 11:09:06', 12345),
    (5, 1, 8, 9, 2014, 2500, '2014-10-14 11:09:07', 12345),
    (6, 1, 8, 9, 2014, 1500, '2014-10-14 11:09:08', 12345),
    (7, 1, 4, 10, 2014, 3500, '2014-10-14 11:09:09', 12345),
    (8, 1, 4, 10, 2014, 2500, '2014-10-14 11:09:10', 12345),
    (9, 1, 4, 10, 2014, 1500, '2014-10-14 11:09:11', 12345),
    (10, 2, 4, 10, 2014, 2500, '2014-10-14 11:09:12', 12345),
    (11, 3, 4, 10, 2014, 1500, '2014-10-14 11:09:13', 12345)
;

查询1

--CREATE View ViewGoal AS 
Select 
  m2.GoalID,
  m2.GoalTypeID,
  b.Type,
  m2.GymID,
  m2.Month,
  m2.Year,
  m2.Goal,
  m2.Created,
  m2.CreatedBy,
  m3.GoalID As LastGoalID,
  m3.Goal As LastValue,
  m3.Created AS LastSet,
  m3.CreatedBy as LastSetBy
FROM 
  (
    SELECT m.*,row_number() over (
    partition by GoalTypeID,GymID,Month,Year
    order by Created desc,GymID,Month,Year) as rn
    From Goal m
  ) m2 -- thanks https://stackoverflow.com/a/1049835/121668
JOIN 
  GoalType b 
  ON m2.GoalTypeID = b.GoalTypeID
LEFT JOIN 
  (
    SELECT m.*,row_number() over (
    partition by GoalTypeID,GymID,Month,Year
    order by Created desc,GymID,Month,Year) as rn
    From Goal m
  ) m3
  ON m2.rn+1 = m3.rn 
    AND m2.GoalTypeID = m3.GoalTypeID 
    AND m2.GymID = m3.GymID 
    AND m2.Month = m3.Month 
    AND m2.Year = m3.Year
WHERE m2.rn=1

结果

| GOALID | GOALTYPEID |          TYPE | GYMID | MONTH | YEAR | GOAL |                        CREATED | CREATEDBY | LASTGOALID | LASTVALUE |                        LASTSET | LASTSETBY |
|--------|------------|---------------|-------|-------|------|------|--------------------------------|-----------|------------|-----------|--------------------------------|-----------|
|      9 |          1 |         Price |     4 |    10 | 2014 | 1500 | October, 14 2014 11:09:11+0000 |     12345 |          8 |      2500 | October, 14 2014 11:09:10+0000 |     12345 |
|      6 |          1 |         Price |     8 |     9 | 2014 | 1500 | October, 14 2014 11:09:08+0000 |     12345 |          5 |      2500 | October, 14 2014 11:09:07+0000 |     12345 |
|      3 |          1 |         Price |     8 |    10 | 2014 | 1500 | October, 14 2014 11:09:05+0000 |     12345 |          2 |      2500 | October, 14 2014 11:09:04+0000 |     12345 |
|     11 |          3 | Type2 Revenue |     4 |    10 | 2014 | 1500 | October, 14 2014 11:09:13+0000 |     12345 |     (null) |    (null) |                         (null) |    (null) |
|     10 |          2 |       Revenue |     4 |    10 | 2014 | 2500 | October, 14 2014 11:09:12+0000 |     12345 |     (null) |    (null) |                         (null) |    (null) |

Answer 1

使用LEAD尝试此方法。

;WITH CTE AS
(
SELECT M.*,ROW_NUMBER() OVER (
    PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR) AS RN,
    LEAD(GOALID) OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTGOALID,
    LEAD (GOAL)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTVALUE,
    LEAD (CREATED)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTSET,
    LEAD (CREATEDBY)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTSETBY
     FROM GOAL M

)
SELECT GOALID,A.GOALTYPEID,B.TYPE,GYMID,
       MONTH,YEAR,GOAL,CREATED,CREATEDBY,
       LASTGOALID,LASTVALUE,LASTSET,LASTSETBY
FROM   CTE A JOIN GOALTYPE B
       ON A.GOALTYPEID = B.GOALTYPEID
WHERE  A.RN = 1