用多对多连接sqlite中的表 [英] Join tables in sqlite with many to many

问题描述

我有以下数据库架构:

create table people (
    id integer primary key autoincrement,
);

create table groups (
    id integer primary key autoincrement,
);

而且我已经在一个单独的文件中知道了哪些人是哪些组的成员(比如在 (person id, group id 的元组中).我该如何构建我的数据库模式可以轻松访问一个人的组，也可以轻松访问组的成员?读取我目前拥有的元组既困难又缓慢，所以我希望它采用数据库形式.我可以'没有像 member1、member2 之类的东西作为列，因为组中的人数目前没有限制.

and I already have which people are members of which groups in a separate file (let's say in tuples of (person id, group id). How can I structure my database schema such that it's easy to access a person's groups, and also easy to access the members of a group? It is difficult and slow to read the tuples that I currently have, so I want this to be in database form. I can't have things like member1, member2, etc. as columns because the number of people in a group is currently unlimited.

推荐答案

将您的文本文件移动到数据库表中

Move your text file into a database table

CREATE TABLE groups_people (
  groups_id integer,
  people_id integer,
  PRIMARY KEY(group_id, people_id)
);  

并选择所有属于第 7 组的人

And select all people that are a member of group 7

SELECT * FROM people p  
  LEFT JOIN groups_people gp ON gp.people_id = p.id  
  WHERE gp.groups_id = '7';

并选择第 5 个人所在的所有组

And select all the groups that person 5 is in

SELECT * FROM groups g  
  LEFT JOIN groups_people gp ON gp.groups_id = g.id  
  WHERE gp.people_id = '5';

这篇关于用多对多连接sqlite中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！