SELECT MIN Date（使用WINDOW函数）和MAX Date（使用JOIN的相关字段）在一起

2 回答

• 0

  这是一个经典的 top-n-per-group 问题，您需要做两次 .

  Sample data

  DECLARE @T TABLE (ID int, Sdate date, Edate date, Reason varchar(50), Type char(1));
  INSERT INTO @T (ID, Sdate, Edate, Reason, Type) VALUES
  (1, '2016-05-30', NULL         , NULL , 'A'),
  (1, '2016-01-19', '2016-12-15' , 'USD', 'B'),
  (1, '2016-11-20', '2016-10-01' , 'IT' , 'B'),
  (2, '2016-10-25', NULL         , NULL , 'A'),
  (2, '2016-09-22', '2016-07-11' , 'SD' , 'B'),
  (2, '2016-02-13', NULL         , NULL , 'A'),
  (2, '2016-01-01', '2016-04-03' , 'IT' , 'B');
  

  对行进行两次编号 .

  PARTITION BY ID ORDER BY Type, Sdate 将为每个 ID 和最小的 Sdate 和 Type=A 提供行号1 .

  PARTITION BY ID ORDER BY Type DESC, Edate DESC 将为每个 ID 和最大的 Edate 和 Type=B 提供行号1 .

  SELECT
      ID
      ,Sdate
      ,Edate
      ,Reason
      ,Type
      ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type, Sdate) AS rnA
      ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type DESC, Edate DESC) AS rnB
  FROM @T
  ORDER BY ID, Type, Sdate
  ;
  

  Intermediate result

  +----+------------+------------+--------+------+-----+-----+
  | ID |   Sdate    |   Edate    | Reason | Type | rnA | rnB |
  +----+------------+------------+--------+------+-----+-----+
  |  1 | 2016-05-30 | NULL       | NULL   | A    |   1 |   3 |
  |  1 | 2016-01-19 | 2016-12-15 | USD    | B    |   2 |   1 |
  |  1 | 2016-11-20 | 2016-10-01 | IT     | B    |   3 |   2 |
  |  2 | 2016-02-13 | NULL       | NULL   | A    |   1 |   3 |
  |  2 | 2016-10-25 | NULL       | NULL   | A    |   2 |   4 |
  |  2 | 2016-01-01 | 2016-04-03 | IT     | B    |   3 |   2 |
  |  2 | 2016-09-22 | 2016-07-11 | SD     | B    |   4 |   1 |
  +----+------------+------------+--------+------+-----+-----+
  

  现在我们需要获取 rnA=1 和 rnB=1 的行，并将它们组合在一起 ID .

  Query

  WITH
  CTE
  AS
  (
      SELECT
          ID
          ,Sdate
          ,Edate
          ,Reason
          ,Type
          ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type, Sdate) AS rnA
          ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type DESC, Edate DESC) AS rnB
      FROM @T
  )
  ,CTE_A
  AS
  (
      SELECT
          ID
          ,Sdate
      FROM CTE
      WHERE rnA = 1
  )
  ,CTE_B
  AS
  (
      SELECT
          ID
          ,Edate
          ,Reason
      FROM CTE
      WHERE rnB = 1
  )
  SELECT
      CTE_A.ID
      ,CTE_A.Sdate
      ,CTE_B.Edate
      ,CTE_B.Reason
  FROM
      CTE_A
      INNER JOIN CTE_B ON CTE_B.ID = CTE_A.ID
  ORDER BY ID
  ;
  

  Result

  +----+------------+------------+--------+
  | ID |   Sdate    |   Edate    | Reason |
  +----+------------+------------+--------+
  |  1 | 2016-05-30 | 2016-12-15 | USD    |
  |  2 | 2016-02-13 | 2016-07-11 | SD     |
  +----+------------+------------+--------+
  

  回复于 2024-05-04T04:25:59+08:00
• 1

  你可以试试这个：

  SELECT
      t.ID
      ,t.SDate
      ,t.EDate
      ,Reason
  FROM (
  SELECT  ID, MIN(CASE WHEN Type='A' THEN SDate END) as SDate , max(CASE WHEN Type='B' THEN EDate END ) as EDate
  FROM T1
  group by ID) t 
  INNER JOIN T1 on t.ID = T1.id and t.EDate = T1.Edate and type = 'B'
  

  要么

  WITH CTE AS
  (
  SELECT  ID, MIN(CASE WHEN Type='A' THEN SDate END) as SDate , max(CASE WHEN Type='B' THEN EDate END ) as EDate
  FROM T1
  group by ID
  )
  SELECT
       CTE.ID AS [Customer ID]
      ,CTE.SDate AS StartDate
      ,CTE.EDate AS Enddate
      ,T1.Reason AS [Discharge Reason]
  
  FROM CTE
  INNER JOIN T1 
      ON CTE.ID=T1.ID AND CTE.Edate=T1.EDAte and T1.type = 'B'
  

  如果有类型B没有重复的最大日期，如果有，你需要稍微调整一下 .

  回复于 2024-05-04T04:25:59+08:00