代码之家 › 专栏 › 技术社区 › Sinan Ünür

如何使用join而不是in来选择个人所属的所有组的名称?

subquery join sql

Sinan Ünür · 技术社区 · 15 年前

请考虑以下简化示例:

CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name VARCHAR(100) );

CREATE TABLE people ( pid INTEGER PRIMARY KEY );

CREATE TABLE people_groups (
    gid INTEGER NOT NULL
        CONSTRAINT fk_people_groups_group
        REFERENCES groups(gid),
    pid INTEGER NOT NULL
        CONSTRAINT fk_people_groups_person
        REFERENCES people(pid),
    CONSTRAINT pk_people_groups PRIMARY KEY (gid, pid)
);

INSERT INTO people (pid) VALUES (1);
INSERT INTO groups (gid, name) VALUES (1, 'One');
INSERT INTO groups (gid, name) VALUES (2, 'Two');
INSERT INTO people_groups (gid, pid) VALUES (1,1);
INSERT INTO people_groups (gid, pid) VALUES (2,1);

SELECT gid, name FROM groups WHERE gid IN (
    SELECT gid FROM people_groups WHERE pid = 1
);

此输出:

1|One
2|Two

正确的是什么 JOIN 最后一次 SELECT ?

6 回复 | 直到 15 年前

Heinzi 15 年前

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid = 1

IN pid=2

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid IN (1, 2)

DISTINCT SELECT GROUP BY g.gid, g.name JOIN

Oded 15 年前

SELECT g.gid, g.name 
FROM groups g 
  INNER JOIN people_groups pg
  ON g.gid = pg.gid
WHERE pg.pid = 1

Sinan Ünür 15 年前

SELECT gid, name FROM groups 
NATURAL JOIN people_groups 
WHERE pid = 1;

Jonathan Leffler 15 年前

SELECT g.gid, g.name
  FROM groups AS g JOIN people_groups AS p ON g.gid = p.gid
 WHERE p.pid = 1;

Sinan Ünür 15 年前

PRIMARY KEY (pid, gid)

SELECT  g.gid
FROM    people_groups pg
JOIN    groups g
ON      g.gid = pg.gid
WHERE   pd.pid = 1

people_groups JOIN pid

CREATE UNIQUE INDEX ux_peoplegroup_p_g ON people_group (pid, gid)

CREATE INDEX ix_peoplegroup_p ON people_group (pid)

InnoDB

Sinan Ünür 15 年前

SELECT g.gid, g.name FROM groups g, people_groups pg 
WHERE g.gid = pg.gid and pg.pid = 1

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

9 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

9 月前

John D · 需要为NULL或NOT NULL的WHERE子句

10 月前

ojek · 如何对SQL结果进行分组和编号?

10 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

10 月前

Sax · 规范化Google表格(第一步)

10 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

10 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

10 月前

Andrus · 如何在sql中查找第二个匹配项

10 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

10 月前