代码之家 › 专栏 › 技术社区 › zgirod

在SQLServer2008中如何确定全局和本地的最小值和最大值?

algorithm sql-server

zgirod · 技术社区 · 15 年前

我使用的是Sql Server 2008。

Start Date      Stop Date       PersonID
12/31/2007      3/31/2008      1
3/31/2008       6/30/2008      1
6/30/2008       9/30/2008      1
2/28/2008       3/31/2008      2
3/31/2008       4/30/2008      2
7/31/2008       8/31/2008      2
5/31/2008       6/30/2008      3

我知道有些日期是每季度一次,有些是每月一次。

最终结果应该是:

StartDate      StopDate      PersonID
12/31/2007     9/30/2008     1
2/28/2008      4/30/2008     2
7/31/2008      8/31/2008     2
5/31/2008      6/30/2008     3

有什么想法吗?

2 回复 | 直到 15 年前

Lieven Keersmaekers 15 年前

CTE ,下面的脚本将生成所需的结果。

测试数据

DECLARE @Users TABLE (
  PersonID INTEGER
  , StartDate DATETIME
  , StopDate DATETIME
)

INSERT INTO @Users
SELECT           1, '12-31-2007', '3-31-2008'
UNION ALL SELECT 1, '3-31-2008','6-30-2008'
UNION ALL SELECT 1, '6-30-2008','9-30-2008'
UNION ALL SELECT 2, '2-28-2008','3-31-2008'
UNION ALL SELECT 2, '3-31-2008','4-30-2008'
UNION ALL SELECT 2, '7-31-2008','8-31-2008'
UNION ALL SELECT 3, '5-31-2008','6-30-2008'

SQL语句

;WITH q AS (
    SELECT  PersonID, StartDate, StopDate
    FROM    @Users
    UNION ALL 
    SELECT  u.PersonId, q.StartDate, u.StopDate
    FROM    q
            INNER JOIN @Users u ON u.PersonID = q.PersonID
                                   AND u.StartDate = q.StopDate
)
SELECT  PersonID, MIN(StartDate), StopDate
FROM    (
          SELECT  PersonID, StartDate, [StopDate] = MAX(StopDate)
          FROM    q
          GROUP BY
                  PersonID, StartDate
        ) p
GROUP BY
        PersonID, StopDate                          
ORDER BY
        PersonID, 2, 3