mysql - 數據庫JOIN查詢
問題描述
問題解答
回答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 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
相關文章:
1. javascript - sublime快鍵鍵問題2. javascript - immutable配合react提升性能?3. css - 寫頁面遇到個布局問題,求大佬們幫解答,在線等,急!~4. javascript - nodejs關于進程間發送句柄的一點疑問5. Apache 已經把網站根目錄的改為allow from all了,但是服務器還是不能訪問?6. 實現bing搜索工具urlAPI提交7. 配置Apache時,添加對PHP的支持時語法錯誤8. vue.js - Vue 如何像Angular.js watch 一樣監聽數據變化9. javascript - 移動端上不能實現拖拽布局嗎?10. phpstudy8.1支持win11系統嗎?

網公網安備