sql-server - Transact SQL 如何在毕业变化(促销)时标记 person_id

Question

我想创建一个列来标记员工，只要他们得到晋升(等级变化)，如下例所示:

+-----------+------------+------------+-------+------+
| PERSON_ID | DATE_START |  DATE_END  | GRADE | Flag |
+-----------+------------+------------+-------+------+
|       614 | 12/12/2012 | 12/12/2013 |     3 |    0 |
|       614 | 12/12/2013 | 12/31/2013 |     3 |    0 |
|       614 | 1/1/2014   | 5/31/2015  |     2 |    1 |
|       616 | 2/1/2009   | 12/31/2011 |     4 |    0 |
|       616 | 1/1/2012   | 12/31/2012 |     4 |    0 |
|       616 | 1/1/2013   | 12/31/2016 |     2 |    1 |
|       616 | 1/1/2017   | 12/31/4712 |     1 |    1 |
|       617 | 2/26/2012  | 12/31/2012 |     5 |    0 |
+-----------+------------+------------+-------+------+

Answer 1

如果您想要新成绩生效的日期，请查看此方法:http://sqlfiddle.com/#!18/ba18c/3/0

或者如果您想要标记旧成绩的最后日期:http://sqlfiddle.com/#!18/ba18c/4/0

这里第二种方法更详细一些:

DECLARE @t TABLE(
  PERSON_ID int
  ,DATE_START date
  ,DATE_END date
  ,GRADE int
 )

 INSERT INTO @t
 VALUES
 (414         ,'12/12/2012'  ,'12/12/2013'  ,3)
,(414         ,'12/12/2013'  ,'12/31/2013'  ,3)
,(414         ,'1/1/2014'    ,'5/31/2015'   ,2)
,(616         ,'2/1/2009'    ,'12/31/2011'  ,4)
,(616         ,'1/1/2012'    ,'12/31/2012'  ,4)
,(616         ,'1/1/2013'    ,'12/31/2016'  ,2)
,(616         ,'1/1/2017'    ,'12/31/4712'  ,1)

SELECT *
      ,CASE
         WHEN grade != ISNULL(LEAD(grade) OVER (PARTITION BY person_id ORDER BY date_start), grade) THEN 1
         ELSE 0
       END AS Flag
  FROM @t
  ORDER BY person_id, date_start

诀窍是将当前成绩与同一个人的下一个成绩进行比较。这是由 LEAD() 和 PARTITION BY 完成的。为了以正确的顺序进行比较，您必须添加 ORDER BY。最后但并非最不重要:IS NULL 是必需的，以便正确考虑当前人员的最后一条记录 - 如果没有找到当前人员的更多记录，LEAD 将提供 NULL用户。