消除PostgreSQL SELECT语句中的重复行

2 回答

• 34

  PostgreSQL当前不允许含糊不清的 GROUP BY 语句，其结果取决于扫描表的顺序，使用的计划等 . 标准表示它应该如何工作AFAIK，但是一些数据库（如5.7之前的MySQL版本）允许更宽松的查询，只选择出现在 SELECT 列表但不在 GROUP BY 中的元素遇到的第一个值 .

  在PostgreSQL中，您应该使用DISTINCT ON进行此类查询 .

  你想写下这样的东西：

  SELECT DISTINCT ON (anwendung.name) anwendung.name, autor.entwickler
  FROM author 
  left join anwendung on anwendung.name = autor.anwendung;
  

  （根据后续评论更正的语法）

  这有点像MySQL 5.7的 ANY_VALUE(...) group by 的伪函数，但反过来 - 它表示 distinct on 子句中的值必须是唯一的，并且任何值都可以接受未指定的列 .

  除非有 ORDER BY ，否则没有保证选择了哪些值 . 您通常应该有 ORDER BY 的可预测性 .

  还注意到使用像 min() 或 max() 这样的聚合会起作用 . 虽然这是真的 - 并且将导致可靠和可预测的结果，不像使用 DISTINCT ON 或一个ambigious GROUP BY - 由于需要额外的排序或聚合而具有性能成本，并且它仅适用于序数据类型 .

  回复于 2024-04-27T07:14:11+08:00
• 12

  Craig的答案和你在评论中得到的查询共享相同的缺陷：表 anwendung 位于 right side of a LEFT JOIN ，这与你明显的意图相矛盾 . 你关心 anwendung.name 并选择 autor.entwickler arbitrarily . 我会再回到那里了 .

  它应该是：

  SELECT DISTINCT ON (1) an.name, au.entwickler
  FROM   anwendung an
  LEFT   JOIN autor au ON an.name = au.anwendung;
  

  DISTINCT ON（1）只是DISTINCT ON（an.name）的语法简写 . 这里允许进行位置参考 .

  如果某个应用程序（ anwendung ）有多个开发人员（ entwickler ），则会选择一个开发人员 arbitrarily . 如果你想要"first"（按字母顺序根据你的语言环境），你必须添加一个 ORDER BY 子句：

  SELECT DISTINCT ON (1) an.name, au.entwickler
  FROM   anwendung an
  LEFT   JOIN autor au ON an.name = au.anwendung
  ORDER  BY 1, 2;
  

  正如@mdahlman暗示的那样，一种更规范的方式是：

  SELECT an.name, min(au.entwickler) AS entwickler
  FROM   autor au
  LEFT   JOIN anwendung an ON an.name = au.anwendung
  GROUP  BY an.name;
  

  ---

  或者，更好的是，清理数据模型，正确实现 anwendung 和_1015128之间的 n:m relationship ，添加代理主键为 anwendung 和 autor 几乎不是唯一的，使用外键约束强制关系完整性并调整生成的查询：

  正确的方法

  CREATE TABLE autor (
     autor_id serial PRIMARY KEY -- surrogate primary key
   , autor    text NOT NULL);
  
  INSERT INTO autor  VALUES
     (1, 'mike')
   , (2, 'joe')
   , (3, 'jane')   -- worked on two apps
   , (4, 'susi');  -- has no part in any apps (yet)
  
  CREATE TABLE anwendung (
     anwendung_id serial PRIMARY KEY -- surrogate primary key
   , anwendung    text  UNIQUE);     -- disallow duplicate names
  
  INSERT INTO anwendung  VALUES
     (1, 'foo')    -- has 3 authors linked to it
   , (2, 'bar')
   , (3, 'shark')
   , (4, 'bait');  -- has no authors attached to it (yet).
  
  CREATE TABLE autor_anwendung (  -- you might name this table "entwickler"
     autor_id     integer REFERENCES autor     ON UPDATE CASCADE ON DELETE CASCADE
   , anwendung_id integer REFERENCES anwendung ON UPDATE CASCADE ON DELETE CASCADE
   , PRIMARY KEY (autor_id, anwendung_id)
  );
  
  INSERT INTO autor_anwendung VALUES
   (1, 1)
  ,(2, 1)
  ,(3, 1)
  ,(2, 2)
  ,(3, 3);
  

  此查询每个应用程序检索一行，其中一个关联作者（按字母顺序排列第一个）或如果没有则检索NULL：

  SELECT DISTINCT ON (1) an.anwendung, au.autor
  FROM   anwendung an
  LEFT   JOIN autor_anwendung au_au USING (anwendung_id)
  LEFT   JOIN autor au USING (autor_id)
  ORDER  BY 1, 2;
  

  结果：

  name  | entwickler
  -------+-----------------
   bait  |
   bar   | joe
   foo   | jane
   shark | jane
  

  回复于 2024-04-27T07:14:11+08:00