Sql 如何用3个(半)固定位置建模团队，并有效地搜索相同不同的团队

我提出这个关系设计:

CREATE TABLE team (
  team_id        int2 PRIMARY KEY
, remaining_time float NOT NULL  -- ?? use date/time type for temporal data!
, description    text NOT NULL   -- ? description?
, difficulty     text NOT NULL
);

CREATE TABLE person (
  pers_id   int2 PRIMARY KEY
, full_name text NOT NULL
  -- other attributes
);

CREATE TABLE team_member (
  team_id int2 NOT NULL REFERENCES team
, pers_id int2 NOT NULL REFERENCES person
, level   int2 NOT NULL CHECK (level BETWEEN 1::int2 AND 5::int2)  -- can be from 1 to 5
, slot    "char" NOT NULL DEFAULT 'l'::"char"
, pos     "char" NOT NULL DEFAULT '1'::"char"
, CONSTRAINT team_member_pkey PRIMARY KEY (team_id, pers_id)
  -- team can have 1 leader and 2 members
, CONSTRAINT chk_1lead_2members CHECK (slot = 'l' AND pos = '1'
                                    OR slot = 'm' AND pos IN ('2', '3'))
  -- 'l' ... leader, 'm' ... member
  -- leader must have pos '1', member must have pos '2' or '3'
, CONSTRAINT uni_team_slot_pos UNIQUE(slot, pos, team_id) INCLUDE (pers_id)
  -- team must have distinct persons; plus: provides useful index
);

-- add idx with reversed cols
CREATE UNIQUE INDEX team_member_pers_id_team_id ON team_member(pers_id, team_id);

fiddle

NOT NULL、CHECK、UNIQUE、PRIMARY KEY和FOREIGN KEY constraints的组合强制您的模型.你仍然可以输入不完整的团队(不是所有的插槽都被填满)，允许中间状态.但这些都被排除在我下面的问题中.

表team_member有充分的理由将限制因素和索引混合使用.参见:

• Is a composite index also good for queries on the first field?

我保留了你 Select 的100，但使表team和person的PK匹配.不要在这里引入类型不匹配.我甚至将team_member行的最小有效负载大小保持在8个字节(也许这就是您优化的目的？)但是，除非你有good reasons，加上可以可靠地rule out overflow，否则用integer(甚至bigint)代替.不容易出错.只增加了很少的成本.

注意数据类型100用于廉价枚举.(可选)不要和char或varchar混淆！参见:

• Any downsides of using data type "text" for storing strings?

使用date/time data type表示日期/时间数据(列remaining_time).不是float.

不要在没有充分理由的情况下混合使用表名的单数和复数形式.

使用合法的、小写的、不带引号的标识符.特别是，要避免reserved words.请参见:

• Are PostgreSQL column names case-sensitive?

查询

已经对性能进行了一些优化.

Distinct teams

完整的团队名单.从骗局中挑选ID最小的队伍.不完整的团队.

SELECT DISTINCT ON (l.pers_id, m.m1, m.m2)
       team_id, l.pers_id AS lead, m.m1, m.m2
FROM   team_member l
JOIN  (
   SELECT team_id, min(pers_id) AS m1, max(pers_id) AS m2
   FROM   team_member m
   WHERE  m.slot = 'm'
   GROUP  BY 1
   HAVING count(*) = 2  -- exclude incomplete teams
   ) m USING (team_id)
WHERE  l.slot = 'l'
ORDER  BY l.pers_id, m.m1, m.m2, team_id;  -- pick team with smallest ID from dupes

参见:

• Select first row in each GROUP BY group?

Same teams

Select 与(1, 2, 3)—1队相同的球队作为领先者.再次，排除不完整的团队.

SELECT team_id, l.pers_id AS lead, m1.pers_id AS m1, m2.pers_id AS m2
FROM   team_member l
JOIN   team_member m1 USING (team_id)
JOIN   team_member m2 USING (team_id)
WHERE  l.slot = 'l'
AND    l.pers_id = 1  -- lead
AND    m1.slot = 'm'
AND    m1.pers_id = LEAST (2,3)  -- member 1
AND    m2.slot = 'm'
AND    m2.pers_id = GREATEST (2,3)   -- member 2
ORDER  BY lead, m1, m2;

fiddle