Postgres SELECT GROUP BY最畅销项目by publisher by publisher
Postgres SELECT GROUP BY最畅销项目by publisher by publisher
提问于 2013-03-07 02:54:35
我有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回答 2
Stack Overflow用户
回答已采纳
发布于 2013-03-07 03:22:32
select
distinct on (pub_name),
pub_name,
book_name,
selling
from (
select
publisher.pub_name,
book.book_name,
sum(rank.selling) selling
from
publisher
inner join
book on book.id_pub = publisher.id
inner join
rank on rank.id_book = book.id
group by 1, 2
) s
order by pub_name, s.selling descStack Overflow用户
发布于 2013-03-07 03:07:02
使用窗口函数。参见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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15255633
复制相关文章
相似问题
腾讯云开发者
