mysql - 数据库JOIN查询

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