Postgresql 左连接没有重复

Question

数据库结构:

CREATE TABLE page
(
        id serial primary key,
        title VARCHAR(40) not null
);

CREATE TABLE page_rating
(
    id serial primary key,
    page_id INTEGER,
    rating_type INTEGER,
    rating INTEGER
);

CREATE TABLE user_history
(
    id serial primary key,
    page_id INTEGER
)

数据:

INSERT INTO page (id,title) VALUES(1,'Page #1');
INSERT INTO page (id,title) VALUES(2,'Page #2');
INSERT INTO page (id,title) VALUES(3,'Page #3');
INSERT INTO page (id,title) VALUES(4,'Page #4');
INSERT INTO page (id,title) VALUES(5,'Page #5');


INSERT INTO page_rating VALUES (1,1,60,100);
INSERT INTO page_rating VALUES (2,1,99,140);
INSERT INTO page_rating VALUES (3,1,58,120);
INSERT INTO page_rating VALUES (4,1,70,110);


INSERT INTO page_rating VALUES (5,2,60,50);
INSERT INTO page_rating VALUES (6,2,99,60);
INSERT INTO page_rating VALUES (7,2,58,90);
INSERT INTO page_rating VALUES (8,2,70,140);

目的——在按“rating_page.rating”排序的表“page”中为rating_type选择唯一值。并从结果中排除表 user_history

我的查询:

SELECT DISTINCT ON(pr.rating_type) p.*,pr.rating,pr.rating_type FROM page as p
    LEFT JOIN page_rating as pr ON p.id = pr.page_id
    LEFT JOIN user_history uh ON uh.page_id = p.id 
    WHERE 
        pr.rating_type IN (60, 99, 58, 45, 73, 97, 55, 59, 70, 43, 74, 97, 64, 71, 46) 
        AND uh.page_id IS NULL 

    ORDER BY pr.rating_type,pr.rating DESC

结果:

ID  TITLE      RATING RATING_TYPE   
1  "Page #1"   120    58
1  "Page #1"   100    60
2  "Page #2"   140    70
1  "Page #1"   140    99

重复值(理想:

ID  TITLE      RATING RATING_TYPE   
1  "Page #1"   120    58
1  "Page #2"   50     60

谢谢帮助!

Answer 1

您几乎肯定需要对表“page_rating”中的 {page_id, rating_type} 进行 UNIQUE 约束。您还缺少所有 必要的外键约束。 “user_history”上的主键也很可疑。

Purpose - select unique values for rating_type ​​in a table "page" sorted by "rating_page.rating".

您可以为 rating_type 选择不同的值，而无需引用任何其他表格。一开始你应该这样做。让我们看一下数据。

select page_id, rating_type, rating
from page_rating
order by page_id, rating_type;

page_id rating_type  rating--1       58           120 *1       60           1001       70           1101       99           1402       58            902       60            50 *2       70           1402       99            60

You seem to want one row per page_id. Those rows are marked with an asterisk in the table above. How can we get those two rows?

Those rows have different values for rating_type, so we can't just use rating_type in the WHERE clause. The values in rating are neither the max nor the min for both values of rating_type, so we can't use GROUP BY with max() or min(). And we can't use GROUP BY with an aggregate function, because you want the unaggregated value of "rating" for an arbitrary value of "rating_type".

So, based on what you've told us, the only way to get the result set you want is to specify rating_type and page_id in the WHERE clause.

select page_id, rating_type, rating
from page_rating
where (page_id = 1 and rating_type = 58)
   or (page_id = 2 and rating_type = 60)
order by page_id, rating_type;

page_id rating_type  rating--1       58           120 2       60            50 

我不会跟进连接，因为我 100% 确信您不真的想这样做。