我可以对这个多对多联接表进行哪些其他查询? [英] What other queries can I do with this many to many joining table?

问题描述

所以..我正在学习mysql中的多对多关系.

so..I am learning many to many relationship in mysql.

我创建了以下表格.

create table post(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(40),
    content text,
    created int(11) default 0
);

create table category(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(35)
);

create table author(
    id int not null primary key auto_increment,
    name varchar(20)
);

create table postcategory(
    id int not null primary key auto_increment,
    post_id int,
    category_id int,
    foreign key(category_id) references category(id) 
    on delete no action,
    foreign key(post_id) references post(id)
    on delete no action
);

这些是我插入的行

insert into author(name) values('James');
insert into author(name) values('Moon');
insert into author(name) values('Min');

insert into category(name) values('C#');
insert into category(name) values('Art');
insert into category(name) values('PHP');
insert into category(name) values('Programming');

insert into post(title) values ('Introduction to C#');
insert into post(title) values ('Modern Art');
insert into post(title) values ('PHP Classes');
insert into post(title) values ('Classes in C#');
insert into post(title) values ('Polymorphism in C#');
insert into post(title) values ('Classic Art');
insert into post(title) values ('OOP in PHP');



insert into postcategory(post_id,category_id) values(1,1);
insert into postcategory(post_id,category_id) values(4,1);
insert into postcategory(post_id,category_id) values(5,1);

insert into postcategory(post_id,category_id) values(2,2);
insert into postcategory(post_id,category_id) values(2,6);

insert into postcategory(post_id,category_id) values(3,3);
insert into postcategory(post_id,category_id) values(3,7);


insert into postcategory(post_id,category_id) values(1,4);
insert into postcategory(post_id,category_id) values(4,4);
insert into postcategory(post_id,category_id) values(5,4);

insert into postcategory(post_id,category_id) values(3,4);
insert into postcategory(post_id,category_id) values(7,4);

基本上，我有帖子，作者，类别和一个名为postcategory的联接表.

basically I have post, author, category, and a joining table named postcategory.

到目前为止，我可以想到两个带有postcategory表的查询.

so far, I can think of two queries with postcategory table.

1. 查询C#类别中的所有帖子
2. 查询帖子ID为#1的所有类别

是否还有其他查询(我确定有..)可以与后类别表一起使用?

Are there any other queries (I am sure there are..) that I can use with postcategory table?

我只是想学习很多对很多关系的使用案例.

I'm Just trying to learn many to many relationship usage cases.

推荐答案

如何列出类别，以及每个类别中有多少帖子，从帖子数最多的类别到最少的类别进行排序.

How about list the categories, and how many posts in each category, sorted from the category with the most posts to the category with the least.

这篇关于我可以对这个多对多联接表进行哪些其他查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！