数据库JOIN查询

Question

我有两个数据库表格， 一个是Article（文章)， 一个是Category(类型）， 是多对多的关系。

但是有的时候Article有可能是没有Category的， 另外就是Article的有一个字段是设置过期时间（expired_at)

我的问题是如果我想查询所有的类型，每个类型里面没有过期的文章的数量，应该怎么做

Answer 1

drop table if exists article;
drop table if exists category;
drop table if exists r_ac;

create table article(
id serial not null,
title varchar(100),
expire timestamp,
primary key(id)
);

create table category(
id serial not null,
name varchar(50),
primary key(id)
);

create table r_ac(
article int not null,
category int not null,
primary key(article, category)
);



insert into article(title, expire) values ('a', '2017-05-20'),('b', null),('c', '2017-03-04'),('d', '2017-02-23'),('e', '2017-04-23'),('f', '2016-09-15'),('g', '2017-06-09');
insert into category(name) values ('c1'),('c2'),('c3'),('c4'),('c5'),('c6'),('c7');
insert into r_ac (article, category) values
(1, 1), (1, 2), (1, 5), (1, 7),
(2, 1), (2, 6),
(3, 5),
(4, 1), (4, 4),
(7, 1), (7, 7);



select category, c.name, count(1) as c from r_ac as ac
inner join (
select id, title, expire from article where expire is null or expire>now()
) as z on ac.article=z.id
left join category as c on ac.category=c.id
group by category, c.name;

Answer 2

select c.id,count(a.id) from category c LEFT JOIN r_ac r on r.category=c.id
LEFT JOIN article a on a.id=r.article and ifnull(a.expire>NOW(),1)
GROUP BY c.id