时间戳

首页 > 解决方案 > 如何在名称列上区分并仅显示该列 PostgreSQL 的最高 ts_rank

postgresql - 如何在名称列上区分并仅显示该列 PostgreSQL 的最高 ts_rank

问题描述

我在这里发布了一个关于此的问题，但后来我意识到我想要的不仅仅是我所要求的。

我实际上需要在name列上以最高为 DISTINCT ts_rank，所以我的代码是，

SELECT name
    ,ts_rank(to_tsvector(name), query) + ts_rank(to_tsvector(content), query2) AS rank
FROM users
INNER JOIN microposts ON users.id = microposts.user_id
    ,plainto_tsquery('re') query
    ,plainto_tsquery('comics') query2
WHERE users.name @@ query
OR microposts.content @@ query2
ORDER BY rank DESC;

给

╔════════════════╤═════════════════════════════════════════╤═══════════╗
║ name           │ content                                 │ rank      ║
╠════════════════╪═════════════════════════════════════════╪═══════════╣
║ Dawson Kreiger │ dc comics dc comics dc comics dc comics │ 0.0919062 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Kaylin Green   │ dc comics dc comics dc comics           │ 0.0889769 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Dawson Kreiger │ dc comics dc comics                     │ 0.0827456 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Kaylin Green   │ dc comics                               │ 0.0759909 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Dawson Kreiger │ I went to the beach dc comics           │ 0.0607927 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Dawson Kreiger │ I went to the beach dc comics           │ 0.0607927 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Kaylin Green   │ I went to the beach dc comics           │ 0.0607927 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Kaylin Green   │ I went to the beach dc comics           │ 0.0607927 ║
╚════════════════╧═════════════════════════════════════════╧═══════════╝

所以我需要输出是这样的，

╔════════════════╤═════════════════════════════════════════╤═══════════╗
║ name           │ content                                 │ rank      ║
╠════════════════╪═════════════════════════════════════════╪═══════════╣
║ Dawson Kreiger │ dc comics dc comics dc comics dc comics │ 0.0919062 ║
╟────────────────┼─────────────────────────────────────────┼───────────╢
║ Kaylin Green   │ dc comics dc comics dc comics           │ 0.0889769 ║
╚════════════════╧═════════════════════════════════════════╧═══════════╝

所以我需要选择一个名称不同且具有最高rank. 但是代码如何知道如何选择具有最高 ts_rank 的不同用户？

编辑

例如，如果我这样做

SELECT name
    , ts_rank(to_tsvector(name), query) + ts_rank(to_tsvector(content), query2) AS rank 
FROM
    (
     SELECT DISTINCT name FROM users WHERE rank = MAX(rank)
     ) 
INNER JOIN microposts ON users.id=microposts.user_id
    , plainto_tsquery('re') query
    ,plainto_tsquery('comics') query2 
WHERE users.name @@ query 
OR microposts.content @@ query2 
ORDER BY rank DESC;

我明白了error: column "rank" does not exist

标签： postgresql

解决方案

你可以GROUP BY 用 a 做 a MAX。

SELECT name
    ,MAX(ts_rank(to_tsvector(name), query) + ts_rank(to_tsvector(content), query2)) AS rank
FROM users
INNER JOIN microposts ON users.id = microposts.user_id
    ,plainto_tsquery('re') query
    ,plainto_tsquery('comics') query2
WHERE users.name @@ query
OR microposts.content @@ query2
GROUP BY name
ORDER BY rank DESC;

推荐阅读