Join for SQL访问Access数据库的问题

2 回答

• 2

  您在外部连接条件中使用的别名不正确 . 您使用 t2 ，它引用子查询中的表，而不是使用 s2 ，它正确引用您的子查询本身 . 试试这个版本：

  SELECT 
      t1.CH_Name_Initials AS FirstName,
      t1.surname AS Surname,
      t1.dt_start AS StartDate,
      t1.dt_DOB AS DateofBirth
  FROM 
      tb_Pers t1
  INNER JOIN 
  (
      SELECT CH_Name_Initials, surname, MAX(dt_start) AS startdate, dt_DOB
      FROM tb_Pers t2
      GROUP BY CH_Name_Initials, surname, dt_DOB
  ) s2
      ON t1.CH_Name_Initials = s2.CH_Name_Initials AND 
         t1.surname = s2.surname AND
         t1.dt_start = s2.startdate AND
         t1.dt_DOB = s2.dt_DOB
  

  回复于 2024-04-25T22:18:38+08:00
• 0

  你想引用t2而不是s2，你可以尝试下面

  Select 
    t1.CH_Name_Initials as FirstName,
    t1.surname as Surname,
    t1.dt_start as StartDate,
    t1.dt_DOB as DateofBirth
  from 
    tb_Pers t1
  inner join 
    (SELECT CH_Name_Initials, surname, dt_start,MAX(dt_start) as max_date, 
     dt_DOB 
  FROM 
    tb_Pers t2
  group by 
    CH_Name_Initials,
    t2.surname,
    t2.dt_start,
    t2.dt_DOB)
  t2
  on 
    t1.CH_Name_Initials = t2.CH_Name_Initials 
  and   t1.surname = t2.surname 
  and   t1.dt_start = t2.dt_start 
  and   t1.dt_DOB = t2.dt_DOB;
  

  你能这样试试吗：

  with cte as (
  select max(dt_start) from tb_Pers
  )
  select CH_Name_Initials as FirstName,
    surname as Surname,
    dt_start as StartDate,
    dt_DOB as DateofBirth 
    from
  tb_Pers,cte where dt_start = cte.max;
  

  回复于 2024-04-25T22:18:38+08:00