首页 / 技术问答 / Sql问答列表 / 问答详情

SQL计数条目大于日期，包括交叉表中的零

我有一个数据库，由一个类别表，和2个数据表(照片和视频标题)

两个数据表与类别表的关系(即哪些照片和视频属于每个类别)由两个连接表(PHOTO_CATEGORES和VIDEO_CATEGORIES)维护

这个 struct 和一些示例数据可以在这里找到:http://sqlfiddle.com/#!5/197c9/1

并在这里转载:

CREATE TABLE categories(category_id INTEGER PRIMARY KEY, title TEXT NOT NULL);

CREATE TABLE photos(photo_id INTEGER PRIMARY KEY, title TEXT NOT NULL, date_added TEXT NOT NULL, date_last_modified TEXT NOT NULL);
CREATE TABLE videos(video_id INTEGER PRIMARY KEY, title TEXT NOT NULL, date_added TEXT NOT NULL, date_last_modified TEXT NOT NULL);

CREATE TABLE photo_categories(photo_id INTEGER, category_id INTEGER, FOREIGN KEY(photo_id) REFERENCES photos(photo_id) ON DELETE CASCADE, FOREIGN KEY(category_id) REFERENCES categories(category_id) ON DELETE CASCADE);
CREATE TABLE video_categories(video_id INTEGER, category_id INTEGER, FOREIGN KEY(video_id) REFERENCES videos(video_id) ON DELETE CASCADE, FOREIGN KEY(category_id) REFERENCES categories(category_id) ON DELETE CASCADE);

INSERT INTO categories(title) VALUES("Animals");
INSERT INTO categories(title) VALUES("People");
INSERT INTO categories(title) VALUES("Nature");
INSERT INTO categories(title) VALUES("Buildings");
INSERT INTO categories(title) VALUES("Vehicles");
INSERT INTO categories(title) VALUES("Computers");

INSERT INTO photos(title, date_added, date_last_modified) VALUES("Man walking his dog", "2023-01-01", "2023-01-01");
INSERT INTO photos(title, date_added, date_last_modified) VALUES("Bird sat on roof of house", "2023-01-01", "2023-01-01");
INSERT INTO photos(title, date_added, date_last_modified) VALUES("Birds sat in a tree", "2022-01-01", "2022-01-01");
INSERT INTO photos(title, date_added, date_last_modified) VALUES("Car outside house", "2020-01-01", "2020-01-01");
INSERT INTO photos(title, date_added, date_last_modified) VALUES("Couple walking on beach", "2021-01-01", "2021-01-01");

INSERT INTO videos(title, date_added, date_last_modified) VALUES("Horses running", "2022-01-01", "2022-01-01");
INSERT INTO videos(title, date_added, date_last_modified) VALUES("Cars racing", "2022-01-01", "2022-01-01");
INSERT INTO videos(title, date_added, date_last_modified) VALUES("Fish in river", "2022-01-01", "2022-01-01");
INSERT INTO videos(title, date_added, date_last_modified) VALUES("Computer loading", "2020-01-01", "2020-01-01");
INSERT INTO videos(title, date_added, date_last_modified) VALUES("Crowd cheering", "2021-01-01", "2021-01-01");

INSERT INTO photo_categories(photo_id, category_id) VALUES(1, 1);
INSERT INTO photo_categories(photo_id, category_id) VALUES(1, 2);
INSERT INTO photo_categories(photo_id, category_id) VALUES(2, 1);
INSERT INTO photo_categories(photo_id, category_id) VALUES(2, 3);
INSERT INTO photo_categories(photo_id, category_id) VALUES(3, 1);
INSERT INTO photo_categories(photo_id, category_id) VALUES(3, 4);
INSERT INTO photo_categories(photo_id, category_id) VALUES(4, 4);
INSERT INTO photo_categories(photo_id, category_id) VALUES(4, 5);
INSERT INTO photo_categories(photo_id, category_id) VALUES(5, 2);
INSERT INTO photo_categories(photo_id, category_id) VALUES(5, 3);

INSERT INTO video_categories(video_id, category_id) VALUES(1, 1);
INSERT INTO video_categories(video_id, category_id) VALUES(2, 5);
INSERT INTO video_categories(video_id, category_id) VALUES(3, 1);
INSERT INTO video_categories(video_id, category_id) VALUES(3, 3);
INSERT INTO video_categories(video_id, category_id) VALUES(4, 6);
INSERT INTO video_categories(video_id, category_id) VALUES(5, 2);

我有一个查询，它会产生照片数量的计数，以及每个类别下的视频数量的计数，如果适用，包括零. 它的工作原理如下:

SELECT
    photos_group.category_id,
    photos_group.title,
    photos_group.num_of_photos,
    videos_group.num_of_videos
from (
select
    categories.category_id,
    categories.title,
    count(photos.photo_id)
AS
    num_of_photos 
FROM
    categories
left JOIN
    photo_categories
ON
    (photo_categories.category_id = categories.category_id) 
left join
     photos
ON
    (photo_categories.photo_id = photos.photo_id)
GROUP BY
    categories.category_id
    ) photos_group
left join (
    select
categories.category_id,
    count(videos.video_id)
AS
    num_of_videos 
FROM
    categories
left JOIN
    video_categories
ON
    (video_categories.category_id = categories.category_id) 
left join
     videos
ON
    (video_categories.video_id = videos.video_id)
GROUP BY
    categories.title

    ) videos_group
    on photos_group.category_id = videos_group.category_id;

因此，使用上面的示例数据，这将产生如下正确的结果:

-------------------------------------------------------------------
|  category_id  |  title     |  num_of_photos   |  num_of_videos  |
-------------------------------------------------------------------
|      1    | Animals    |        3         |         2       |
|      2    |  People    |        2         |         1       |
|      3    |  Nature    |        2         |         1       |
|      4    | Buildings  |        2         |         0       |
|      5    |  Vehicles  |        1         |         1       |
|      6    | Computers  |        0         |         1       |
-------------------------------------------------------------------

我想在此查询中添加一个条件，以计算DATE_ADDED和DATE_MODIFIED字段大于某个值的条目数(包括零

例如，在这些字段为&gt；=2021-01-1的情况下，结果应返回，

-------------------------------------------------------------------
|  category_id  |  title     |  num_of_photos   |  num_of_videos  |
-------------------------------------------------------------------
|      1    | Animals    |        3         |         2       |
|      2    |  People    |        2         |         1       |
|      3    |  Nature    |        2         |         1       |
|      4    | Buildings  |        1         |         0       |
|      5    |  Vehicles  |        0         |         1       |
|      6    | Computers  |        0         |         0       |
-------------------------------------------------------------------

但我不清楚在哪里添加WHERE条件，以确保结果表保持上面的 struct ，但计数已更新.

例如，如果我try 在每个SELECT的GROUP_BY之前添加WHERE子句，计数是不正确的，并且也不包括零计数，并且在有照片而不是视频的结果的位置放置一个NULL:

SELECT
    photos_group.category_id,
    photos_group.title,
    photos_group.num_of_photos,
    videos_group.num_of_videos
from (
select
    categories.category_id,
    categories.title,
    photos.date_added,
    count(photos.photo_id)
AS
    num_of_photos 
FROM
    categories
left JOIN
    photo_categories
ON
    (photo_categories.category_id = categories.category_id) 

left join
     photos
ON
    (photo_categories.photo_id = photos.photo_id)
  where
    photos.date_added > 2020-05-01
GROUP BY
    categories.category_id
    ) photos_group
left join (
    select
categories.category_id,
    count(videos.video_id)
AS
    num_of_videos 
FROM
    categories
left JOIN
    video_categories
ON
    (video_categories.category_id = categories.category_id) 
left join
     videos
ON
    (video_categories.video_id = videos.video_id)
where   
  videos.date_added > 2020-05-01
GROUP BY
    categories.title

    ) videos_group
    on photos_group.category_id = videos_group.category_id 
  


-------------------------------------------------------------------
|  category_id  |  title     |  num_of_photos   |  num_of_videos  |
-------------------------------------------------------------------
|      1    | Animals    |        3         |         2       |
|      2    |  People    |        2         |         1       |
|      3    |  Nature    |        2         |         1       |
|      4    | Buildings  |        2         |      (null)     |
|      5    |  Vehicles  |        1         |         1       |
-------------------------------------------------------------------

任何关于我如何实现我想要的结果的指导都将不胜感激.谢谢

推荐答案

DATE_ADDED字段是一个文本字段，因此您需要用引号将您要比较的日期括起来.此外，您还希望从所有类别的 Select 开始，然后将其加入照片搜索和视频搜索.最后，您可以使用ifull将空值替换为零.

SELECT
    cats.category_id,
    cats.title,
    ifnull(photos_group.num_of_photos, 0) as num_of_photos,
    ifnull(videos_group.num_of_videos, 0) as num_of_videos
from (
  select category_id, title from categories
) cats
left join
(
  select category_id, count(photos.photo_id) as num_of_photos from photo_categories
  left join photos using (photo_id)
  where photos.date_added > "2020-05-01"
  group by photo_categories.category_id
) photos_group
on (cats.category_id = photos_group.category_id)
left join (
  select category_id, count(videos.video_id) as num_of_videos from video_categories
  left join videos using (video_id)
  where videos.date_added > "2020-05-01"
  group by video_categories.category_id
) videos_group
on (cats.category_id = videos_group.category_id)

Sql相关问答推荐

BigQuery`喜欢ANY‘和`不喜欢ANY’

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数？

用于匹配红旗和绿旗的SQL查询

Postgres：对包含数字的字符串列表进行排序

连接三个表的正确方式是什么？在这三个表中，可以显示在一个表上的行将在其他表中显示结果

DBeaver将过程中的属性列表转换为字符串

如何隐藏聚合JSON数组元素的键

如何将我的联接数据放入每个用户每月多行的列中？

如何找到一个组合的两个列，这是不是在其他表在ORACLE SQL？

SQL仅返回第一个字母在A-Z之间的值

不同计数的 Postgres PIVOT 表

通过ID和数据找到每个不同的值

在where语句中使用CTE非常缓慢

如何创建一个递归计数器来查找一个元素有多少父级和子级？

多行状态下的分组查询判断状态

自动生成计算频率的列

雅典娜弄错了操作顺序

在 sql 中合并系列以删除重复项

在 UTF 编码字符上应用 SQL LIKE 语句没有给出任何结果

Snowflake SQL group-by 的行为不同，具体取决于列是按位置引用还是按别名引用

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630