SQLite3多个FTS表搜索 [英] SQLite3 multiple FTS tables search

问题描述

我在sqlite数据库中有三个虚拟表格 - 两个有一个内容列，另一个有两列 - 标题和内容。说一张表是针对一些文章的内容和标题，另外两张是针对评论和他们的笔记。有一种方法可以在一个查询中对其进行搜索吗？我需要合并所有结果并按照相关性对它们进行排序，首先是文章及其标题，然后是评论和备注。我做了一些解决方法。我在表格中填入搜索结果以显示每一次新的搜索，但我认为这不是一个好主意：

I have three fts virtual tables in my sqlite database - two with one content column, and one with 2 columns - title and content. Say one table is for some articles' content and titles, and other two is for reviews and their notes. Is there some way to commit a search among them all in one query? I need to merge all results and sort them by relevance, first the articles and their titles, then reviews and notes. I have made some workaround. I fill a table with search results to display on every new search, but I think that is not very good idea:

DROP TABLE IF EXISTS srch_res;
CREATE TABLE srch_res(type integer, docid integer, snippet text, rank numeric);"
INSERT INTO srch_res SELECT '0', docid, snippet(reviews_c), rank(matchinfo(reviews_c), 0.5) FROM reviews_c WHERE reviews_c MATCH '%s';
INSERT INTO srch_res SELECT '1', docid, snippet(notes_c), rank(matchinfo(notes_c), 0.25) FROM notes_c WHERE notes_c MATCH '%s';
INSERT INTO srch_res SELECT '2', docid, snippet(arts_c), rank(matchinfo(arts_c), 1.0, 0.75) FROM arts_c WHERE arts_c MATCH '%s';

推荐答案

使用：

Use:

 TRUNCATE TABLE srch_res;

INSERT INTO srch_res (type, docid, snippet, rank)
    SELECT '0', docid, snippet(reviews_c), rank(matchinfo(reviews_c), 0.5)
      FROM reviews_c
     WHERE reviews_c MATCH '%s'
 UNION ALL
    SELECT '1', docid, snippet(notes_c), rank(matchinfo(notes_c), 0.25)
      FROM notes_c
     WHERE notes_c MATCH '%s'
 UNION ALL
    SELECT '2', docid, snippet(arts_c), rank(matchinfo(arts_c), 1.0, 0.75)
      FROM arts_c
     WHERE arts_c MATCH '%s'
;

这篇关于SQLite3多个FTS表搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！