spiderUrl 表
字段:spiderId , title , content
1 book title, bookcontentspider_downloadFile 表
字段spiderId , downloadId
1 1
1 2downloadFile表
字段, dwnloadId , fileUrl , isDownload
1 1.gif true
2 2.gif falsespiderUrl 代表一个网页, downloadFile代表网页中要下载的文件, spiderUrl,downloadFile 之间的关系是多对多的关系, 中间表为spider_downloadFile我怎么查出一个网页全部的文件已经下载的网页, spiderId 对应的 多个downloadFile表记录isDownload全部为true
字段:spiderId , title , content
1 book title, bookcontentspider_downloadFile 表
字段spiderId , downloadId
1 1
1 2downloadFile表
字段, dwnloadId , fileUrl , isDownload
1 1.gif true
2 2.gif falsespiderUrl 代表一个网页, downloadFile代表网页中要下载的文件, spiderUrl,downloadFile 之间的关系是多对多的关系, 中间表为spider_downloadFile我怎么查出一个网页全部的文件已经下载的网页, spiderId 对应的 多个downloadFile表记录isDownload全部为true
where spiderId not in
(
select spiderId
from spider_downloadFile b,
downloadFile c
where b.downloadId=c.downloadId
and isDownload='false'
)
select 1 spiderId, 'book title' title, 'bookcontent' content from dual union all
select 2 spiderId, 'tile one' title, 'content one' content from dual
),spider_downloadFile as(
select 1 spiderId, 1 downloadId from dual union all
select 1 spiderId, 2 downloadId from dual union all
select 2 spiderId, 3 downloadId from dual union all
select 2 spiderId, 4 downloadId from dual
),downloadFile as
(
select 1 downloadId,'1.gif' fileUrl, 'true' isDownload from dual union all
select 1 downloadId,'2.gif' fileUrl, 'false' isDownload from dual union all
select 3 downloadId,'1.gif' fileUrl, 'true' isDownload from dual union all
select 4 downloadId,'2.gif' fileUrl, 'true' isDownload from dual
)--以上部分为自带例子数据
select t.*
from spiderUrl t
where not exists (select *
from spiderUrl a, spider_downloadFile b, downloadFile c
where a.spiderId = b.spiderId
and b.downloadId = c.downloadId
and c.isDownload = 'false'
and a.spiderId = t.spiderId)
from spiderUrl s
where not exists
(select 1
from downloadfile d, spider_downloadfile sd
where s.spiderid = d.spiderid
and s.spiderid = ds.spiderid
and d.downloadid = ds.downloadid
and ds.isdownload = 'false'
)
where not exists(select 1 from spider_downloadFile b,downloadFile c
where c.isDownload='false' and b.downloadId=c.downloadId and a.spiderId=b.spiderId)
from spiderUrl t
where not exists (select 1
spider_downloadFile b, downloadFile c
where t.spiderId = b.spiderId
and b.downloadId = c.downloadId
and c.isDownload = 'false')