我有一个Access 2007数据库，跟踪文档进展的时间。顺序如下:
- 创建
- 送审
- 对
- 送审 批准
hist_id doc_id month step status datestamp
我创建了一个查询，返回月底的状态，如下所示:
SELECT doc_id, month, step, status, datestamp
FROM hist
WHERE (((hist.datestamp) In
(
Select Top 1 h.[datestamp]
From hist as h
Where h.[doc_id] = hist.[doc_id] and h.[month] = hist.[month]
Order By h.[datestamp] DESC))
)
ORDER BY month, doc_id DESC;
得到……
doc_id month step status datestamp
a 2011-01 2 sent for review 18/01/2011
b 2011-02 1 created 01/02/2011
a 2011-02 3 reviewed 19/02/2011
c 2011-03 1 created 07/03/2011
d 2011-03 1 created 08/03/2011
e 2011-06 1 created 14/06/2011
f 2011-07 1 created 05/07/2011
g 2011-07 4 sent for approval 18/07/2011
h 2011-07 2 sent for review 14/07/2011
f 2011-08 3 reviewed 29/08/2011
g 2011-08 5 approved 17/08/2011
h 2011-08 1 created 10/08/2011
e 2011-09 3 reviewed 17/09/2011
但是我真正需要的是我的查询也返回月份的状态没有改变的文档。例如，文档a的状态在19/02/2011上变为reviewed，但这是它最后一次出现在上面的结果中。它实际上应该在之后的每个月以reviewed的形式出现，直到后来成为sent for approval。
所以我试图修改我的查询(或查询上面的查询)，以提供如下结果…
doc_id month step status datestamp
a 2011-01 2 sent for review 18/01/2011
a 2011-02 3 reviewed 19/02/2011
b 2011-02 1 created 01/02/2011
a 2011-03 3 reviewed 19/02/2011
b 2011-03 1 created 01/02/2011
c 2011-03 1 created 07/03/2011
d 2011-03 1 created 08/03/2011
a 2011-04 3 reviewed 19/02/2011
b 2011-04 1 created 01/02/2011
c 2011-04 1 created 07/03/2011
d 2011-04 1 created 08/03/2011
a 2011-05 3 reviewed 19/02/2011
b 2011-05 1 created 01/02/2011
c 2011-05 1 created 07/03/2011
d 2011-05 1 created 08/03/2011
a 2011-06 3 reviewed 19/02/2011
b 2011-06 1 created 01/02/2011
c 2011-06 1 created 07/03/2011
d 2011-06 1 created 08/03/2011
e 2011-06 1 created 14/06/2011
a 2011-07 3 reviewed 19/02/2011
b 2011-07 1 created 01/02/2011
c 2011-07 1 created 07/03/2011
d 2011-07 1 created 08/03/2011
e 2011-07 1 created 14/06/2011
f 2011-07 1 created 05/07/2011
g 2011-07 4 sent for appr 18/07/2011
h 2011-07 2 sent for rev 14/07/2011
a 2011-08 3 reviewed 19/02/2011
b 2011-08 1 created 01/02/2011
c 2011-08 1 created 07/03/2011
d 2011-08 1 created 08/03/2011
e 2011-08 1 created 14/06/2011
f 2011-08 3 reviewed 29/08/2011
g 2011-08 5 approved 17/08/2011
h 2011-08 1 created 10/08/2011
a 2011-09 3 reviewed 19/02/2011
b 2011-09 1 created 01/02/2011
c 2011-09 1 created 07/03/2011
d 2011-09 1 created 08/03/2011
e 2011-09 1 reviewed 17/09/2011
f 2011-09 3 reviewed 29/08/2011
g 2011-09 5 approved 17/08/2011
h 2011-09 1 created 10/08/2011
谢谢你的帮助…我真的不知道从何说起
这在SQL中工作，不使用任何特殊功能，所以应该在MS-ACCESS SQL中工作。
首先，需要创建一个月表。
create table monthly (monthN char(7))
insert into monthly values('2011-01')
insert into monthly values('2011-02')
...
并填充您需要的所有月份。
建立了这个表后，下面的查询应该返回您正在查找的内容:
select d1.doc_id,d1.monthN,d1.step,d1.status,d1.dateStamp
from monthly m1
join docs d1 on d1.monthN=m1.monthN
union
select d2.doc_id,zz.monthN,d2.step,d2.status,d2.dateStamp
from docs d2
join
(
select aa.doc_id,aa.monthN,bb.EndM from
(
select yy.doc_id,yy.monthN from
(
select d3.doc_id,m2.monthN
from monthly m2
join (select distinct doc_id from docs) d3 on 1=1
) yy
left join docs xx on xx.doc_id=yy.doc_id and xx.monthN=yy.MonthN
where xx.hist_id is null
) aa
join (select doc_id,MIN(monthN) as startM,MAX(monthN) as EndM
from docs group by doc_id)
bb on bb.doc_id=aa.doc_id and aa.monthN>=bb.StartM
) zz
on zz.doc_id=d2.doc_id and zz.EndM=d2.monthN
order by d1.monthN,d1.doc_id
我建议单独运行每个内部查询，以帮助跟踪正在做的事情…
多亏了Sparky的回答，我才能拼凑出一些适合我的东西。
步骤- 创建
months表来包含2011-08这样的月份列表 创建
month_range查询以从实际文档中获取月份范围 SELECT month_no FROM months WHERE month BETWEEN(最小月份)AND(最大月份)
对
month_range和hist表进行跨产品查询，其中hist.month_no<=month_range.month上面的步骤留下一个表，每个文档每月有多个状态更改。在SELECT
中将- INNER JOIN从上面的结果与
hist表使用hist_id来访问状态
month_no和doc_no与max(hist_id)分组我使用的最终查询看起来像这样....
SELECT xx.month_no, xx.swp, xx.hist_id, h.status
FROM
(
SELECT zz.month_no, zz.swp, Max(zz.hist_id) AS hist_id
FROM
(
SELECT * FROM month_range AS mr, hist AS h
WHERE h.[first_of_month] <= mr.[first_of_month]
) zz
GROUP BY zz.month_no, zz.swp
) xx
INNER JOIN hist as h ON xx.hist_id = h.hist_id;