如何在MS Access中通过单SQL查询实现基于双列条件的多表内连接

架构老林

2026-6-23

MS Access多表关联获取STATUS字段的SQL查询方案

完整SQL语句

SELECT 
    PERSON.IDPERSON, 
    MASTERPERSON.PERSONNAME, 
    PERSON.[DATE], 
    MASTERPERSON.LOCATION, 
    FORMAT(PERSON.DATE,'dddd') AS DAYS,
    PERSONDAYS.STATUS
FROM 
    (PERSON INNER JOIN MASTERPERSON ON PERSON.IDPERSON = MASTERPERSON.IDPERSON)
    INNER JOIN PERSONDAYS 
        ON MASTERPERSON.LOCATION = PERSONDAYS.LOCATION 
        AND FORMAT(PERSON.DATE,'dddd') = PERSONDAYS.DAYS;

逻辑说明

将PERSON与MASTERPERSON的关联结果，再和PERSONDAYS做双字段内连接：

通过MASTERPERSON.LOCATION匹配PERSONDAYS.LOCATION
通过格式化后的日期星期值（FORMAT(PERSON.DATE,'dddd')）匹配PERSONDAYS.DAYS

现有表结构

PERSON表

IDPERSON	DATE
1000	29-May-24
1000	30-May-24
1001	29-May-24
1001	30-May-24

MASTERPERSON表

IDPERSON	PERSONNAME	LOCATION
1000	Rey	A
1001	Michael	B

PERSONDAYS表

LOCATION	DAYS	STATUS
A	Wednesday	A10
A	Thursday	A27
B	Wednesday	B18
B	Thursday	B29

当前查询及结果

当前SQL

SELECT PERSON.IDPERSON, MASTERPERSON.PERSONNAME, PERSON.[DATE], MASTERPERSON.LOCATION, FORMAT(PERSON.DATE,'dddd') AS DAYS
FROM PERSON INNER JOIN MASTERPERSON ON PERSON.IDPERSON = MASTERPERSON.IDPERSON;