MS Access查询，包括对自身的联接(使用子查询)并报告不存在的值(null值)

Question

我有一个名为PRICES:的下表

202.3

VEREXID	TIMEID	PRICE
6001	20191231	104.3
6001	20181231	115.3
6001	20171231	138.3
6001	20161231	122.3
6002	20191231	102.3
6002	20190931	123.3
6002	20190631	167.3
6002	20181231
6002	20171231	402.3
6002	20170931	162.3

好了：

SELECT J1.VERTEXID, J1.TIMEID, P4.PRICE
FROM (
SELECT DISTINCT P1.VERTEXID, P3.TIMEID
FROM PRICES P1,
(SELECT DISTINCT TOP 5 P2.TIMEID
FROM PRICES P2
WHERE P2.TIMEID <= 20191231
ORDER BY P2.TIMEID DESC
) P3
) J1
LEFT JOIN PRICES P4 ON J1.VERTEXID = P4.VERTEXID AND J1.TIMEID = P4.TIMEID
ORDER BY J1.VERTEXID, J1.TIMEID DESC

您必须根据需要添加您的筛选条件。

基本上你必须做以下事情：

• 执行CROSS JOIN以获得VERTEXID和TIMEID的所有组合(即J1(
• 则LEFT JOIN将该结果发送到表本身，并获得相应的PRICE值

缺少的价格将为空。

如果您想要每个顶点id的最近5行：

select p.*
from prices as p
where p.vertexid in (6001, 6002) and
p.timeid in (select top 5 p2.timeid
from prices as p2
where p2.vertexid = p.vertexid
order by p2.timeid desc
);

您在查询中对时间进行了筛选，但在您提出的实际问题中没有提到这一点。假设过滤是在timeid上进行的，如果需要，可以将其放入子查询中

编辑：

如果你想要所有vertexid都有相同的五个时间id(如果它们存在的话(，你可以使用：

select p.*
from prices as p
where p.vertexid in (6001, 6002) and
p.timeid in (select top 5 p2.timeid
from prices as p2
where p2.vertexid in (6001, 6002)
order by p2.timeid desc
);

如果您只想共享两个顶点的时间ID，可以使用：

p.timeid in (select top 5 p2.timeid
from prices as p2
where p2.vertexid in (6001, 6002)
group by p2.timeid
having count(*) = 2
order by p2.timeid desc
);