假设我在SSMS中有这样的表A:
员工 | 教学 | START_DATE | END_DATE |
---|---|---|---|
123 | 科学 | ||
123 | 科学 | ||
123 | 科学 | <2002年1月18日>2002年5月8日 | |
123 | 历史 | <2002年8月15日>||
123 | 科学 | ||
123 | 科学 |
我建议以下解决方案:
DECLARE @T TABLE
(
EMPLOYEE int,
TEACHING varchar(MAX),
START_DATE datetime2,
END_DATE datetime2
)
INSERT @T VALUES
(123, 'SCIENCE', '1/1/2001', '5/10/2001'),
(123, 'SCIENCE', '8/20/2001', '12/16/2001'),
(123, 'SCIENCE', '1/18/2002', '5/8/2002'),
(123, 'HISTORY', '8/15/2002', '12/10/2002'),
(123, 'SCIENCE', '1/3/2003', '5/6/2003'),
(123, 'SCIENCE', '8/1/2003', '12/3/2003'),
(124, 'SCIENCE', '1/1/2001', '5/10/2001'),
(124, 'SCIENCE', '8/20/2001', '12/16/2001'),
(124, 'SCIENCE', '1/18/2002', '5/8/2002'),
(124, 'HISTORY', '8/15/2002', '12/10/2002'),
(124, 'SCIENCE', '1/3/2003', '5/6/2003'),
(124, 'SCIENCE', '8/1/2003', '12/3/2003');
WITH Discriminated AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY EMPLOYEE ORDER BY START_DATE)-ROW_NUMBER() OVER (PARTITION BY EMPLOYEE, TEACHING ORDER BY START_DATE) Discriminator
FROM @T
)
SELECT EMPLOYEE, TEACHING, MIN(START_DATE) START_DATE, MAX(END_DATE) END_DATE
FROM Discriminated
GROUP BY EMPLOYEE, TEACHING, Discriminator
ORDER BY EMPLOYEE, START_DATE
我还假设你想单独计算每个员工。诀窍是标记连续的组(请参阅CTE中的查询(。