DB2 中的分组依据

Question

我有一个查询要为 DB2 中的用户获取最近的未来医生预约。

  SELECT user_id, 
         appointment_id, 
         date 
    FROM (SELECT user_id, 
                 appointment_id, 
                 date 
            FROM doctors_appointment 
           WHERE date > current_timestamp 
             AND user_id IN (23, 24, 25)
        ORDER BY date ASC ) as tmp_appointment 
GROUP BY user_id

但是，在 group by 语句中包含 Appointment_id 和 date 会引发错误，这意味着它要求在 group by 中包含所有选择字段。

如果我将所有字段包含在分组中，结果将如下所示：

   user_id    appointment_id      date 
   --------  ----------------     -------
    23            450             2011-07-20
    23            451             2011-07-26
    25            452             2011-07-18

但我需要如下结果：

   user_id    appointment_id      date 
   --------   --------------       -------
    23            450             2011-07-20
    25            452             2011-07-18

Answer 1

我认为这可以解决问题：

  SELECT D.User_ID, D.Appointment_ID, D.Date 
    FROM Doctors_Appointment AS D
    JOIN (SELECT User_ID, MIN(Date) AS Apt_Date
            FROM Doctors_Appointment 
           WHERE Date > current_timestamp 
             AND User_ID IN (23, 24, 25)
           GROUP BY User_ID) AS T 
      ON T.User_ID = D.User_ID AND T.Apt_Date = D.Date;

如果给定用户在同一天有两个约会，它将为该用户生成两行。