首页 文章

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

提问于
浏览 483
0

我试图运行一些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

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

相关问题