如何比较上下级表

Question

我有三个表: crawl_post、crawl_image和crawl_video。

CREATE TABLE crawl_post ( Id int NOT NULL AUTO_INCREMENT, ImageCount int, VideoCount int PRIMARY KEY (Id) );

CREATE TABLE crawl_image ( Id int NOT NULL AUTO_INCREMENT, Status bool PostId int PRIMARY KEY (Id) );

CREATE TABLE crawl_video ( Id int NOT NULL AUTO_INCREMENT, Status bool PostId int PRIMARY KEY (Id) );

Status = true - downloaded, false - not

用一个查询，我想选择所有的帖子，他们的图像和视频已下载？也就是说，count(下载的图像)= post.Imagecount，count(下载的视频)= post.VideoCount。

谢谢你的帮助。

Answer 1

使用join子句很容易实现这一点，如下所示：

select
    c1.*
from
    crawl_post c1
join
    crawl_image c2 on c1.id = c2.postid and c2.status = 1
join
    crawl_video c3 on c1.id = c3.postid and c3.status = 1

上面的SQL将返回下载了其图像和视频的所有帖子的结果。

好的，我试着理解你想要什么，你只是想得到已经完成了所有图片和视频下载操作的帖子，对吗？I reEdit如下：

select cp.* from (
select
    c1.id,
    count(c2.postid) as imageFinishedCount
    count(c3.postid) as videoFinishedCount
from
    crawl_post c1
left join
    crawl_image c2 on c1.id = c2.postid and c2.status = 1
left join
    crawl_video c3 on c1.id = c3.postid and c3.status = 1
group by
    c1.id
) tmp 
join 
    crawl_post cp on tmp.id = cp.id 
where 
    tmp.imageFinishedCount = cp.imageCount and tmp.videoFinishedCount = cp.videoCount

Answer 2

你可以通过应用内部连接来获取帖子，并将下载视频和图片的条件设置为status =1。

Select * from crawl_post cp, crawl_image ci, crawl_video cv 
where cp.id = ci.postid and ci.status = 1 
and cp.id = cv.postid and cv.status =1

Answer 3

我希望这个问题能帮助你实现你的目标。

  Select * from crawl_post 
              left join crawl_image on crawl_post.Id=crawl_image.PostId
              left join crawl_video on crawl_post.Id=crawl_video.PostId
    where crawl_image.Status=true OR crawl_video.Status=true