SELECT语句出现问题-查找不同条件下的最小值和最大值- SQL server 2008

Question

下面是我选择的内容：

DocID   DocNumber   MilestoneDate   MilestoneID
30      14-1        2001-10-01  10
30      14-1        2001-10-15  11
30      14-1        2002-11-06  13
30      14-1        2003-11-01  27
30      14-1        2003-11-01  30
30      14-1        2004-03-01  28
30      14-1        2004-03-01  31
31      14-3        2003-06-27  13
31      14-3        2004-05-05  27
31      14-3        2004-05-05  30
31      14-3        2005-07-20  35
31      14-3        2005-08-31  23
36      1-2         1996-10-01  10
36      1-2         1996-10-01  11
36      1-2         1996-12-01  28
36      1-2         1996-12-01  31

我需要从这个选择中生成一个报告，每个DocID有1行。它应该有列DocID，DocNumber，EarliestDate = if Milestone10或11不为空，从这两个列中选择较大的日期，从其他DocID的MilestoneID中选择最早的日期(这是我的问题所在)，如有任何帮助，将不胜感激。

谢谢

Answer 1

这就是你要找的吗？

DECLARE @Table TABLE (DocID INT, DocNumber VARCHAR(10), MilestoneDate DATETIME, MilestoneID INT)
INSERT INTO @Table VALUES (30, '14-1', '10-01-2001', 10)
INSERT INTO @Table VALUES (30, '14-1', '10-15-2001', 11)
INSERT INTO @Table VALUES (30, '14-1', '11-06-2002', 13)
INSERT INTO @Table VALUES (30, '14-1', '11-01-2003', 27)
INSERT INTO @Table VALUES (30, '14-1', '11-01-2003', 30)
INSERT INTO @Table VALUES (30, '14-1', '03-01-2004', 28)
INSERT INTO @Table VALUES (30, '14-1', '03-01-2004', 31)
INSERT INTO @Table VALUES (31, '14-3', '06-27-2003', 13)
INSERT INTO @Table VALUES (31, '14-3', '05-05-2004', 27)
INSERT INTO @Table VALUES (31, '14-3', '05-05-2004', 30)
INSERT INTO @Table VALUES (31, '14-3', '07-20-2005', 35)
INSERT INTO @Table VALUES (31, '14-3', '08-31-2005', 23)
INSERT INTO @Table VALUES (36, '1-2', '10-01-1996', 10)
INSERT INTO @Table VALUES (36, '1-2', '10-01-1996', 11)
INSERT INTO @Table VALUES (36, '1-2', '12-01-1996', 28)
INSERT INTO @Table VALUES (36, '1-2', '12-01-1996', 31)

-- get the items where we want the max
SELECT DocID, DocNumber, MAX(MilestoneDate) AS MilestoneDate
FROM @Table WHERE MilestoneID IN (10, 11)
GROUP BY DocID, DocNumber
UNION -- get the items where we want the min
SELECT DocID, DocNumber, MIN(MilestoneDate)
FROM @Table 
WHERE DocID NOT IN (SELECT DocID FROM @Table WHERE MilestoneID IN (10, 11))
GROUP BY DocID, DocNumber

Result:
DocID      DocNumber        MilestoneDate
30         14-1             2001-10-15 00:00:00.000
31         14-3             2003-06-27 00:00:00.000
36         1-2              1996-10-01 00:00:00.000

Answer 2

SELECT
    t.DocID,
    t.DocNumber,
    EarliestDate = 
        ISNULL(MAX(CASE WHEN t.MilestoneID IN (10, 11)
                        THEN t.MilestoneDate END),
               MIN(CASE WHEN t.MilestoneID IN (10, 11)
                        THEN NULL ELSE t.MilestoneDate END))
FROM [table] t
GROUP BY
    t.DocID,
    t.DocNumber

Answer 3

我为我的问题带来了一个解决方案。

任何改进的建议都是受欢迎的。由于我需要将pvanhouten给我的建议合并到另一个查询中，因此我决定这样做：

Select t1.DocID, t1.DocNumber, t1.DocTitle, t1.DevStatus, t1.GrOrgType, t1.DocType,  
       t1.WebStatus, t1.RecommandType, t1.ProjectPlanApprovedBySponsor, 
       t1.ProjectPlanApprovedByWG, t1.Completed, t2.MilestoneDate as EarliestDate From
  (Select * From 
   (Select distinct d.DocID, d.DocNumber, d.DocTitle,dm.MilestoneDate,lkpds.DevStatus,   
                    lkpgt.GrOrgType, lkpdt.DocType, lkpws.WebStatus, lkprt.RecommandType,
        CASE dm.MilestoneId 
          WHEN 10 Then 'PPAW'
          WHEN 11 Then 'PPAS'
          WHEN 35 Then 'Completed'
        END as [MilestoneName],   

       (CASE WHEN not dv.DocID  is Null THEN 'Yes' END) OldVersionExist

        FROM Document d LEFT JOIN lkpDocDevStatus lkpds ON d.DocDevStatID = lkpds.DocDevStatID
            LEFT JOIN lkpDocType lkpdt ON d.DocTypeID = lkpdt.DocTypeID
            LEFT JOIN lkpDocRecType lkprt ON d.DocRecTypeID = lkprt.DocRecTypeID
            LEFT JOIN lkpWebStatus lkpws ON d.WebStatID = lkpws.WebStatID
            LEFT JOIN Doc_Group dg ON d.DocID = dg.DocID
            LEFT JOIN GroupOrg gro ON dg.GroupID = gro.GroupID
            LEFT JOIN lkpGroupOrgType lkpgt ON lkpgt.GrOrgTypeID = gro.GrOrgTypeID
            LEFT JOIN Doc_Milestone dm ON d.DocID = dm.DocID
            JOIN Milestones m ON m.MilestoneID = dm.MilestoneID
            LEFT JOIN Doc_Version dv ON d.DocID = dv.DocID

         Where dg.GrOrgRoleID = 5 and lkpds.DocDevStatID <> 1 and  lkpds.DocDevStatID <> 9             

 Group By d.DocID, d.DocNumber, d.DocTitle, lkpds.DevStatus, lkpds.DocDevStatID,  
          lkpgt.GrOrgType, lkpdt.DocType, 
          lkpws.WebStatus, lkprt.RecommandType, dv.docID, dm.MilestoneDate, dm.Milestoneid) T

 PIVOT
 (
  Max(MilestoneDate)
  FOR [MilestoneName] IN ([PPAW],[PPAS],[Completed])
  ) AS PivotTable
 ) t1

 JOIN 
  (SELECT dm.DocID , MAX(MilestoneDate) AS MilestoneDate 
     FROM document d JOIN Doc_Milestone as dm ON d.DocID = dm.DociD WHERE MilestoneID IN (10,   
      11) 
     GROUP BY dm.DocID
     UNION -- get the items where we want the min 
     SELECT dm.DocID, MIN(MilestoneDate) 
     FROM document d JOIN Doc_Milestone as dm ON d.DocID = dm.DociD  
     WHERE dm.DocID NOT IN (SELECT dm.DocID FROM Doc_Milestone as dm WHERE dm.MilestoneID IN (10,
     11)) 
     GROUP BY dm.DocID) as t2

 ON t1.DocID = t2.DocID