Abfrage den höchsten Rang der einzelnen Elemente

Habe ich die(vereinfacht) Folgendes Modell:

Book
  id
  name

BookCategory
  book_id
  category_id
  rank

Category
  id
  name

Mit einer bestimmten Kategorie-id, ich würde gerne die Bücher mit, die Kategorie mit dem höchsten Rang.

Ich gebe Ihnen ein Beispiel, um deutlicher zu sein:

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

Das Ergebnis für category_id = 2 wäre, das Buch mit der id = 3.

Dies ist die Abfrage, die ich gelaufen bin:

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"

Dies ist nicht der richtige Weg, es zu tun, weil Sie nicht wählen Sie den max Rang jedes Buch. Ich habe noch um zu finden eine passende Lösung.

Hinweis: ich verwende die postgresql-version 9.1.

Edit:

DB-Schema (entnommen aus martin ' s SQL Fiddle Antwort):

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');

Wenn man zeigen könnte, wird die Tabelle Struktur und die Daten für dein Beispiel als CREATE TABLE und INSERT Aussagen, die Menschen könnten kopieren und einfügen, um eine test-Datenbank ausprobieren von Lösungen vor der Veröffentlichung.

InformationsquelleAutor Rod0n | 2012-08-08

Einrichten:

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);

Die Lösung:

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;

Logisch, die Unterabfrage in der FROM - Klausel erzeugt eine relation mit der höchsten Kategorie, für jedes Buch, aus dem Sie dann wählen Sie die Bücher in dieser Kategorie und bestellen Sie Sie durch die Rangliste in dieser Kategorie an.

Ergebnisse:

 Namen 
----------- 
Siddharta 
(1 Zeile)

InformationsquelleAutor kgrittn

fügen Sie eine weitere Spalte berechnen Rang:

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

InformationsquelleAutor rs.

1

Ist das, was Sie wollen?
```
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
```
Wenn ich verstehe deine Frage richtig, Sie benötigen, um die maximal für eine gegebene Kategorie, für jedes Buch, das in BookCategory (das ist das, was die innere select-tut) und dann einfach die Verknüpfung der Buch-Tabelle auf book_id.

Das ganze Beispiel ist auf SQL Fiddle

EDIT:

Ich sehe, dass es bereits eine akzeptierte Antwort, aber der Vollständigkeit halber, hier ist meine Antwort nach der Klärung der Frage:
```
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
```
Auf SQL Fiddle.

Danke für deine Antwort martin, aber das Ergebnis ist nicht die richtige. Die Abfrage ist die Rückgabe von Büchern mit der id 1 und 3, aber die richtige Antwort ist nur das Buch mit der id = 3(siddartha). Die max-Reihe für das Buch mit der id = 1 ist das mit der Kategorie-id = 9.

InformationsquelleAutor martin

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.