选择过去100年没有记录的行。

Question

对不起，标题不太好，但我不知道如何恰当地描述我的问题

我正在找一张桌子，上面写着至少100年后发生灾难的城市名称，自从那次灾难发生以来，这座城市至少有100年没有再发生灾难了

因此，我的代码起作用了，但当我的灾难表中只有一个灾难时，城市就不会出现(例如，他在1800年)。

select D1.cname,D1.etype,D1.dyear
from disaster as D1 , disaster as D2
where(D1.dyear<=(date_part('year',current_date)-100)) and((D2.cname=D1.cname)and ((D2.dyear>(D1.dyear+100))))                                                                                     
group by D1.cname,D1.dyear

这是我的数据库

​

​

这就是我从我的查询中得到的

​

​

我想念“慈发”和“鹿儿岛”

我在找你的帮助，谢谢！

加入后，在@Zaynul Abadin Tuhin的帮助下，仍然没有展示只有一场灾难的城市

​

Answer 1

这应该是可行的：

select D1.cname,D2.dtype,D1.dyear
from 
(
  select cname, min(dyear) as dyear
  from disaster
  where dyear<=(date_part('year',current_date)-100)
  and not exists (
    select * from disaster D3 
    where D3.cname = disaster.cname 
    and D3.dyear > disaster.dyear 
    and D3.dyear < disaster.dyear+100
  )
  group by cname
) D1
join disaster D2 on D1.cname = D2.cname and D2.dyear = D1.dyear

它只显示了自上次灾难发生后100年内没有发生过灾难的城市中的最后一次灾难。

Answer 2

使用显式连接，而不是逗号分隔的表名，使两个表之间进行交叉连接。

select distinct D1.cname,D1.etype,D1.dyear
from disaster as D1  join disaster as D2
on (D1.dyear<=(date_part('year',current_date)-100)) and((D2.cname=D1.cname)and ((D2.dyear>(D1.dyear+100))))                                                                                     

我删除了组，因为我没有看到任何聚合。

Answer 3

窗口函数是表达这种逻辑的一种更自然的方式：

select d.*
from (select d.*,
             lag(dyear) over (partition by cname order by dyear) as prev_dyear
      from disaster d
     ) d
where dyear >= date_part('year', current_date) - 100 and
      (prev_dyear is null or prev_dyear < dyear - 100)