sql - Postgresql:根据表列中的最大匹配数对结果进行排序

Question

根据评论编辑

我有 2 个表posts 和categories。结构和样本数据如下:

POSTS
ID    NAME                  DESCRIPTION              CATEGORY_ID
--  -----------------   -------------------          -----------
1   For Song Lovers     A post all about music            8
2   For Music Lovers    About passion of music            8
3   I love songs        Listing favourite songs           8
4   Rock Music          ImagineDragon                     6
5   Retro Music         Old choice musical themes         7

CATEGORIES
ID    NAME
--  -----------------
6   Artists
7   Entertainment
8   Music

我想按以下方式对记录进行排序:

1. 在名称、描述和类别名称中具有匹配的关键字的帖子首先出现，然后是
2. 帖子名称中包含匹配的关键字，描述后跟
3. 帖子名称中包含匹配的关键字，后跟
4. 描述中包含匹配的关键字的帖子，后跟
5. 在类别中具有匹配的关键字的帖子

我想要的结果输出是(如果keyword=music):

OUTPUT
---------
Post ID#2
Post ID#5
Post ID#4
Post ID#1
Post ID#3

我已经能够编写 5 个查询并将它们组合起来以获得唯一的记录。但这不是优化的解决方案。这是我尝试过的:

(
SELECT "posts".* FROM "posts" INNER JOIN "categories" ON "categories"."id" = "posts"."category_id" WHERE posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') AND categories.name iLIKE ('%music%')
+
SELECT "posts".* FROM "posts" WHERE posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%')
+
SELECT "posts".* FROM "posts"  WHERE posts.name iLIKE ('%music%')
+
SELECT "posts".* FROM "posts"  WHERE posts.description iLIKE ('%music%')
+
SELECT "posts".* FROM "posts" INNER JOIN "categories" ON "categories"."id" = "posts"."category_id" WHERE categories.name iLIKE ('%music%')
).uniq

我什至尝试编写一个查询，但没有返回我想要的结果(原因很明显，我没有使用 GROUP OR COUNT)

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE (
  (posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') AND categories.name iLIKE ('%music%'))
  OR (posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%'))
  OR (posts.name iLIKE ('%music%'))
  OR (posts.description iLIKE ('%music%'))
  OR (categories.name iLIKE ('%music%')))

请建议我如何使用上述 SQL 实现相同的效果。

环境细节:

数据库:PostgreSQL

版本:postgres (PostgreSQL) 9.4.5

Answer 1

据我所知，PostgreSQL 支持 bool 值，所以这应该符合您的描述，按单独的列排序:

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE  -- can be simplified to 
      LOWER(posts.name)        iLIKE ('%music%')
   OR LOWER(posts.description) iLIKE ('%music%')
   OR LOWER(categories.name)   iLIKE ('%music%')
ORDER BY -- FALSE probably sorts lower than TRUE, thus DESC
  LOWER(posts.name)        iLIKE ('%music%') DESC, posts.name,
  LOWER(posts.description) iLIKE ('%music%') DESC, posts.description
  LOWER(categories.name)   iLIKE ('%music%') DESC, categories.name

另注:iLike不区分大小写，那你为什么要申请LOWER呢？这应该返回相同的结果:

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE  -- can be simplified to 
      posts.name        iLIKE ('%music%')
   OR posts.description iLIKE ('%music%')
   OR categories.name   iLIKE ('%music%')
ORDER BY -- FALSE probably sorts lower than TRUE, thus DESC
   posts.name        iLIKE ('%music%') DESC, posts.name,
   posts.description iLIKE ('%music%') DESC, posts.description
   categories.name   iLIKE ('%music%') DESC, categories.name

现在将其与@JuanCarlosOropeza 的回答结合起来:-)