postgresql 查询以获取每个项目的最高排名

Query to get the highest rank of each item

提问by Rod0n

I've the(simplified) following model:

我有（简化）以下模型：

Book
  id
  name

BookCategory
  book_id
  category_id
  rank

Category
  id
  name

With a given category id, I'd like to get the books having that category as the highest ranked one.

使用给定的类别 id，我想将具有该类别的书籍作为排名最高的书籍。

I'll give an example to be more clear about it:

我将举一个例子来更清楚地了解它：

Book   

id    name   
---  -------
1     On Writing
2     Zen teachings
3     Siddharta

BookCategory   

book_id category_id      rank   
---       -------        -----
1        2               34.32
1        5               24.23
1        9               54.65
2        5               27.33
2        9               28.32
3        2               30.43
3        5               27.87

Category   

id    name   
---  -------
2     Writing
5     Spiritual
9     Buddism

The result for category_id = 2 would be the book with id = 3.

category_id = 2 的结果将是 id = 3 的书。

This is the query I'm running:

这是我正在运行的查询：

SELECT book."name" AS bookname
FROM bookcategory AS bookcat
LEFT JOIN book ON bookcat."book_id" = book."id" 
LEFT JOIN category cat ON bookcat."category_id" = cat."id" 
WHERE cat."id" = 2
ORDER BY bookcat."rank"

This is not the right way to do it because it doesn't select the max rank of each book. I've yet to find a proper solution.

这不是正确的方法，因为它没有选择每本书的最大排名。我还没有找到合适的解决方案。

Note: I'm using the postgresql 9.1 version.

注意：我使用的是 postgresql 9.1 版本。

Edit:

编辑：

DB Schema (taken from martin's SQL Fiddle answer):

数据库架构（取自马丁的 SQL Fiddle 答案）：

create table Book (
  id int,
  name varchar(16)
  );

insert into Book values(1, 'On Writing');
insert into Book values(2, 'Zen teachings');
insert into Book values(3, 'Siddharta');

create table BookCategory (
  book_id int,
  category_id int,
  rank real
  );

insert into BookCategory values(1,2,34.32);
insert into BookCategory values(1,5,24.23);
insert into BookCategory values(1,9,54.65);
insert into BookCategory values(2,5,27.33);
insert into BookCategory values(2,9,28.32);
insert into BookCategory values(3,2,30.43);
insert into BookCategory values(3,5,27.87);

create table Category (
  id int,
  name varchar(16)
  );

insert into Category values(2, 'Writing');
insert into Category values(5,'Spiritual');
insert into Category values(9,    'Buddism');

采纳答案by kgrittn

To set up:

建立：

CREATE TABLE Book
(
  id int PRIMARY KEY,
  name text not null
);

CREATE TABLE Category
(
  id int PRIMARY KEY,
  name text not null
);

CREATE TABLE BookCategory
(
  book_id int,
  category_id int,
  rank numeric not null,
  primary key (book_id, category_id)
);

INSERT INTO Book VALUES
  (1, 'On Writing'),
  (2, 'Zen teachings'),
  (3, 'Siddharta');

INSERT INTO Category VALUES
  (2, 'Writing'),
  (5, 'Spiritual'),
  (9, 'Buddism');

INSERT INTO BookCategory VALUES
  (1, 2, 34.32),
  (1, 5, 24.23),
  (1, 9, 54.65),
  (2, 5, 27.33),
  (2, 9, 28.32),
  (3, 2, 30.43),
  (3, 5, 27.87);

The solution:

解决方案：

SELECT Book.name
  FROM (
         SELECT DISTINCT ON (book_id)
             *
           FROM BookCategory
           ORDER BY book_id, rank DESC
       ) t
  JOIN Book ON Book.id = t.book_id
  WHERE t.category_id = 2
  ORDER BY t.rank;

Logically, the subquery in the FROMclause generates a relation with the highest ranking category for each book, from which you then select the books in that category and order them by the ranking in that category.

从逻辑上讲，FROM子句中的子查询会为每本书生成一个与排名最高的类别的关系，然后您可以从中选择该类别中的书籍并按该类别中的排名对它们进行排序。

Results:

结果：

   name    
-----------
 Siddharta
(1 row)

回答by rs.

add another column to calculate rank:

添加另一列来计算排名：

dense_rank() OVER (PARTITION BY book."name" ORDER BY bookcat."rank"
s ASC) AS rank

回答by martin

Is this what you want?

这是你想要的吗？

SELECT 
  book.name, mx.max_rank
FROM
  (SELECT 
     max(rank) AS max_rank , book_id 
   FROM BookCategory WHERE category_id = 2 
   GROUP BY 
     book_id
  ) mx
JOIN Book ON 
  mx.book_id = Book.id

If I understand your question correctly, you need to get the maximum for a given category for every book in BookCategory (that is what the inner select does) and then simply join it to the Book table on book_id.

如果我正确理解您的问题，您需要为 BookCategory 中的每本书获取给定类别的最大值（这就是内部选择所做的），然后只需将其加入 book_id 上的 Book 表。

The whole example is on SQL Fiddle

整个示例在SQL Fiddle 上

EDIT:

编辑：

I see that there is already an accepted answer, but for the sake of completeness, here is my answer following the clarification of the question:

我看到已经有一个可以接受的答案，但为了完整起见，这是我在澄清问题后的答案：

SELECT 
  Book.name 
FROM
  (SELECT max(rank) AS max_rank, book_id AS bid
   FROM BookCategory GROUP BY book_id
  ) mx
JOIN BookCategory ON
  rank = max_rank
  AND book_id = bid
JOIN Book
  ON book_id = Book.id
WHERE category_id = 2

On SQL Fiddle.

在SQL Fiddle 上。