sql - 在常规订购之前定制订购？

Question

我有 3 个表:

CREATE TABLE items (
    id integer PRIMARY KEY,
    title varchar (256) NOT NULL
);

INSERT INTO items (id, title) VALUES (1, 'qux');
INSERT INTO items (id, title) VALUES (2, 'quux');
INSERT INTO items (id, title) VALUES (3, 'quuz');
INSERT INTO items (id, title) VALUES (4, 'corge');
INSERT INTO items (id, title) VALUES (5, 'grault');

CREATE TABLE last_used (
    item_id integer NOT NULL REFERENCES items (id),
    date integer NOT NULL
);

INSERT INTO last_used (item_id, date) VALUES (2, 1000);
INSERT INTO last_used (item_id, date) VALUES (3, 2000);
INSERT INTO last_used (item_id, date) VALUES (2, 3000);

CREATE TABLE rating (
    item_id integer NOT NULL REFERENCES items (id),
    rating integer NOT NULL
);

INSERT INTO rating (item_id, rating) VALUES (1, 400);
INSERT INTO rating (item_id, rating) VALUES (2, 100);
INSERT INTO rating (item_id, rating) VALUES (3, 200);
INSERT INTO rating (item_id, rating) VALUES (4, 300);
INSERT INTO rating (item_id, rating) VALUES (5, 500);

我想按以下顺序选择行:

1. 最近使用的项目与搜索字符串匹配；
2. 评分最高的项目与搜索字符串匹配；
3. 与搜索字符串匹配的所有其他项目。

对于搜索 i.title ~* '(?=.*u)'，我得到:

   id   |   title   |   max(last_used.date)   |   rating.rating   
   3    |   quuz    |          2000           |        200
   2    |   quux    |          3000           |        100
   5    |   grault  |          null           |        500
   1    |   qux     |          null           |        400

...使用以下代码:

WITH used AS (
    SELECT lu.item_id
        FROM last_used lu
    JOIN (
        SELECT item_id, max(date) AS date
        FROM last_used
        GROUP BY 1
    ) sub USING (date)
 -- WHERE lu.user_id = 1
    ORDER BY lu.date DESC
)
SELECT i.id, i.title, r.rating
    FROM items i
    LEFT JOIN rating r
        ON r.item_id = i.id
    WHERE
        i.title ~* '(?=.*u)'
    ORDER BY
        i.id NOT IN (SELECT item_id FROM used),
        r.rating DESC NULLS LAST
    LIMIT 5 OFFSET 0

是否可以得到如下结果(最近用过的在前)？

   id   |   title   |   max(last_used.date)   |   rating.rating   
   2    |   quux    |          3000           |        100
   3    |   quuz    |          2000           |        200
   5    |   grault  |          null           |        500
   1    |   qux     |          null           |        400

Answer 1

您可以使用以下查询来获取所需的订单

通过带有 l.date DESC NULLS LAST, r.rating DESC NULLS LAST 的 ORDER BY 子句:

SELECT i.id, i.title, l.date, r.rating
    FROM items i
    LEFT JOIN rating r
        ON r.item_id = i.id
    LEFT JOIN ( SELECT item_id, max(date) AS date FROM last_used GROUP BY 1 ) l
        ON l.item_id = i.id        
    WHERE
        i.title ~* '(?=.*u)'
    ORDER BY l.date DESC NULLS LAST, r.rating DESC NULLS LAST
    LIMIT 5 OFFSET 0;

Demo