以下内容为我提供了有关记录的完整列表:
SELECT ID, NAME, STATE, VDATE, COMPLETE_DATE
from db
where NAME NOT IN (SELECT NAME from db where COMPLETE_DATE != '')
ORDER BY NAME, STATE, VDATE DESC;
不幸的是,我对所有名称(根据Vdate)的所有记录都感兴趣。基本上,完全相同的结果减去包含最新VDATE的每个名称/状态组合的记录。对于每个名称,状态组合,可能都有10个记录,每个记录都具有相同或不同的VDATE。
如何消除此数据集中的最新VDATE?
获取每个名称,状态并在此后使用not in
的最大VDATE。
SELECT ID, NAME, STATE, VDATE, COMPLETE_DATE
from db
where (NAME,STATE,VDATE) NOT IN (SELECT NAME,STATE,MAX(VDATE) as MAXVDT
from db
where COMPLETE_DATE != ''
GROUP BY NAME,STATE)
ORDER BY NAME, STATE, VDATE DESC
或使用 join
与派生表中包含每个名称和状态的最大日期。
select d1.ID, d1.NAME, d1.STATE, d1.VDATE, d1.COMPLETE_DATE
from db d1
join (SELECT NAME,STATE,MAX(VDATE) as MAXVDT
from db
where COMPLETE_DATE != ''
GROUP BY NAME,STATE) d2
on d1.name = d2.name and d1.state = d2.state
where d1.vdate <> d2.maxvdt
order by d1.NAME, d1.STATE, d1.VDATE desc
请注意,如果每个名称只有一个vdate,则状态组合两个查询都不会返回这些名称,结果中的状态组合,因为该日期是该组合的max
VDATE。