主页 / dba / 问题 / 327270
Accepted
Lance
Asked: 2023-05-19 07:29:02 +0800 CST

如何按 PostgreSQL 中的连接表属性排序？

  • 772

我是 SQL 的新手，并且大部分时间都在使用 ORM（好吧，SQL 构建器，而不是原始 SQL）。我在Vercel Postgres版本 15中有一个简单的字典应用程序，具有这种模式：

CREATE TABLE words (
  id serial PRIMARY KEY,
  transcription_count INTEGER NOT NULL,
  pronunciation_count INTEGER NOT NULL,
  verified BOOLEAN NOT NULL
);

CREATE TABLE transcriptions (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  system INTEGER NOT NULL,
  length INTEGER NOT NULL
);

CREATE TABLE pronunciations (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  syllable_count INTEGER NOT NULL
);

例如，我想找到转录文本长度 >= 5 的所有单词，并对文本进行排序，但返回单词。每个转录都属于一个系统，每个系统可以有 1 个或多个转录（例如，给定“拉丁”系统，单词“color/colour”有 2 个拼写，作为一个粗略的例子）。此外，每个单词可以有多个系统（例如，中文有拼音和汉字作为 2 个可能的系统，在中文系统中有繁体和简体作为系统内的 2 个转录）。

这就是我的想法。

SELECT id from words
INNER JOIN transcriptions t ON t.word_id = words.id
WHERE t.length >= 5
AND t.system = 1
ORDER BY t.text ASC

这是正确的查询类型吗？这会返回重复的单词吗，因为我正在按连接表排序，并且存在 1<>many 关系？我不清楚这将如何工作/会返回什么样的结果。另外，作为第二个查询，我怎么能另外指定“只为每个单词选择匹配请求的第一个转录”，所以它只按每个单词的 1 个转录排序？

postgresql

1 个回答

  1. Best Answer

    查询基本有效，但id含糊不清，排序顺序也是如此：

    SELECT w.id  -- table-qualify!
FROM   words w
JOIN   transcriptions t ON t.word_id = w.id
WHERE  t.length >= 5
AND    t.system = 1
ORDER  BY t.text, w.id, t.id;  -- add tiebreaker(s) to make unambiguous

    “只为每个单词选择符合请求的第一个转录”

    走出去，我将其解释为：

    “对每个单词进行最短的符合条件的转录（按字母顺序排在首位），并列出按所选翻译排序的单词。”

    如果你只想返回，我们根本word.id不需要涉及表格：word

    SELECT word_id
FROM  (
   SELECT DISTINCT ON (word_id)
          word_id, text
   FROM   transcriptions t
   WHERE  length >= 5
   AND    system = 1
   ORDER  BY word_id, length, text  -- pick the shortest, then alphabetically first
   ) t
ORDER  BY text, word_id;  -- add tiebreaker to make unambiguous

    别的：

    SELECT w.*
FROM  (
   SELECT DISTINCT ON (word_id)
          word_id AS id, text
   FROM   transcriptions t
   WHERE  length >= 5
   AND    system = 1
   ORDER  BY word_id, length, text  -- pick the shortest, then alphabetically first
   ) t
JOIN   words w USING (id)
ORDER  BY t.text, w.id;  -- add tiebreaker to make unambiguous

    DISTINCT ON对于每个单词很少的翻译通常是有效的。对于许多翻译，其他技术可能更好。看：

    您还将受益于lengthand system（和word_idand text）上的一个或多个索引，但具体是哪个索引，这取决于未公开的细节。

    • 1

相关问题