时间戳

首页 > 解决方案 > 对第三个表按条件过滤的两个表的 Groupby 计数查询

sql - 对第三个表按条件过滤的两个表的 Groupby 计数查询

问题描述

我有三个表：类别、邮件和分类。表之间的关系在此 SQLFiddle 中概述了一些示例数据：http ://sqlfiddle.com/#!9/118b24/3/0

CREATE TABLE `Category` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `Mail` (
  `id` int(6) unsigned NOT NULL,
  `content` varchar(500) NOT NULL,
  `date` datetime NOT NULL,  
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;


CREATE TABLE `Classification` (
  `id` int(6) unsigned NOT NULL,
  `mail_id` int(6) unsigned NOT NULL,
  `category_id` int(6) unsigned NOT NULL,
  FOREIGN KEY (mail_id) REFERENCES Mail(id),
  FOREIGN KEY (category_id) REFERENCES Category(id),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

我首先运行一个查询来获取分配给每个类别的电子邮件数量：

SELECT Category.name, count(Classification.category_id) FROM Category LEFT OUTER JOIN Classification ON Classification.category_id = Category.id GROUP BY Category.name

哪个工作正常。

但我现在想根据电子邮件集合中的日期添加一个过滤器。如果我加入了过滤器集合：

SELECT Category.name, count(Classification.category_id) FROM Category JOIN Mail ON Mail.date < '2019-03-24' LEFT OUTER JOIN Classification ON Classification.category_id = Category.id GROUP BY Category.name

但现在计数只翻了一番，似乎甚至没有应用过滤器。为什么过滤器不工作，我应该怎么做才能修复它？

标签： sql

解决方案

你需要一个JOIN介于Classificationand之间的条件Mail：

SELECT ca.name, count(cl.category_id)
FROM Category LEFT JOIN
     Classification cl
     ON cl.category_id = c.id LEFT JOIN
     Mail m
     ON m.id = cl.mail_id AND  -- your JOIN key goes here
        m.date < '2019-03-24'
GROUP BY c.name ;

推荐阅读