postgresql - Postgres SELECT GROUP BY 出版商最畅销的商品

Question

我有 3 个表:

publisher (id serial pkey, pub_name text)
book (id serial pkey, id_pub INTEGER NOT NULL REFERENCES publisher (id), book_name TEXT)
rank (id SERIAL PKEY, id_book INTEGER NOT NULL REFERENCES book (id), week DATE, selling INTEGER)

并且只想列出每个出版商的畅销书。我现在所拥有的返回所有书籍的销售:

SELECT publisher.pub_name, book.book_name, SUM(rank.selling)
 FROM rank, publisher, book
 WHERE rank.id_book = book.id AND book.id_pub = publisher.id
 GROUP BY publisher.pub_name, book.book_name;

这样的返回:

BigPublisher, Book1, 100300
GoodPublisher, BeBook, 10003
BigPublisher, Book2, 50200
OldPublisher, N-Book, 20009
GoodPublisher, CeBook, 4000
GoodPublisher, DeBook, 3001

我在哪里寻找那种结果:

BigPublisher, Book1, 100300
GoodPublisher, BeBook, 10003
OldPublisher, N-Book, 20009

Answer 1

使用窗口函数。参见 the window functions tutorial in the PostgreSQL documentation .

您正在寻找以下内容:

SELECT
  rank() OVER (PARTITION BY publisher ORDER BY sum_of_sold DESC) AS pos
-- ... FROM clause etc here ...
WHERE pos = 1;