我创建了一个简单的访问数据库,一个表结构和查询的示例。两个桌子,人(包含3个记录(&地址(包含5个记录(,提供为每个人捕获多个地址的能力。我对正常的有条件陈述很好,但是这让我陷入困境...我正在寻找一个只能返回给定人的最新地址的查询。桌子关系
查询的当前SQL:
select person.personid_pk,address.address,address.statusdate来自人的内在加入地址。我当前的回报:
雇员_pk地址状态日期1 12 ELM ST,MN 23569 11/13/20171 15苹果LN,纽约12345 7/15/20182 30 Mulberry,TN 38456 6/11/20182 10寂寞的松树,肯塔基15487 12/4/20183 100 Plaze Place,LA 14563 6/17/2018我需要以最大(最新(的状态返回每个人我的预期回报应该是:
雇员_pk地址状态日期1 15苹果LN,纽约12345 7/15/20182 10寂寞的松树,肯塔基15487 12/4/20183 100 Plaze Place,LA 14563 6/17/2018
您可以使用相关的子查询:
select a.*
from address as a
where a.statusdate = (select max(a2.statusdate)
from address as a2
where a2.EmployeeID_PK = a.EmployeeID_PK
);
使用具有排名函数的CTE整齐起作用。
;WITH empaddress AS
(
SELECT person.personid_pk,
address.address,
address.statusdate,
Dense_rank() OVER (partition BY id ORDER BY statusdate DESC) AS d_rank
FROM person
INNER JOIN address
ON person.[PersonID_PK] = address.personid_fk; )
SELECT person.personid_pk,
address.address,
address.statusdate
FROM empaddress
WHERE d_rank = 1;
感谢您的帮助。我修改了戈登的代码以支持我的查询,以下提供了答案。
SELECT Person.PersonID_PK, Address.Address, Address.StatusDate
FROM Person
INNER JOIN Address
ON Person.[PersonID_PK] = Address.PersonID_FK
WHERE (((Address.StatusDate)=(SELECT MAX(Address.StatusDate)
FROM Address
WHERE Person.PersonID_PK = Address.PersonID_FK)));