mysql - 数据库JOIN查询

问题描述

问题解答

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 acinner join (select id, title, expire from article where expire is null or expire>now()) as z on ac.article=z.idleft join category as c on ac.category=c.idgroup by category, c.name;回答2：

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