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

我试图运行一些SQLcode,返回最大startdate记录 . 我正在使用带有内部联接的Select语句 .

见下面的代码 .

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,
      t1.surname)
s2
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

然而,当我尝试运行它时,我收到错误消息

详细信息:“ODBC:错误[42000] [Microsoft] [ODBC Microsoft Access驱动程序] JOIN操作中的语法错误

我理解Access需要多个括号,但我很难找到放在哪里?

谢谢

克里斯

回答(2)

2 years ago

您在外部连接条件中使用的别名不正确 . 您使用 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

2 years ago

你想引用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;