sqlite - SQLite:搜索关键字的表具有单独的行并联接多个表

Question

我有三个SQLite表，一个产品表，一个keyword_products表和一个关键字表。

CREATE TABLE products (
    product_rec_id  INTEGER  PRIMARY KEY  NOT NULL,
    name            VARCHAR (100)  NOT NULL
);

CREATE TABLE keywords_products (
    keyword_rec_id INTEGER NOT NULL,
    product_rec_id INTEGER NOT NULL
);

CREATE TABLE keywords (
    keyword_rec_id INTEGER  PRIMARY KEY  NOT NULL,
    keyword        VARCHAR (50)  NOT NULL  UNIQUE
);

keyword_products表包含产品的记录ID和关键字的记录ID。可以在关键字表中为每个产品分配多个关键字。

关键字表如下所示：

keyword_rec_id  keyword
--------------  -----------
60              melee
43              scifi
87              water

keyword_products表如下所示（可以将一个关键字分配给许多产品）：

keyword_rec_id  product_rec_id
--------------  --------------
43              1
60              1
43              2
87              3

产品表如下所示：

product_rec_id  name
--------------  --------------
1               Scifi Melee Weapons
2               Scifi Ray Weapon
3               Lake House

很长时间以来，我一直在使用此SQL命令，但我只是注意到，对于AND操作，它显然不起作用，因为所有关键字都位于单独的行中。

SELECT
  products.product_rec_id,
  keywords.keyword
FROM
  products
  LEFT OUTER JOIN keywords_products ON
    products.product_rec_id = keywords_products.product_rec_id
  LEFT OUTER JOIN keywords ON
    keywords_products.keyword_rec_id = keywords.keyword_rec_id
WHERE
  (keywords.keyword LIKE '%scifi%' OR
    keywords.keyword LIKE '%melee%')

有时我希望能够检索与输入的所有关键字（AND）或仅一个关键字（OR）匹配的产品（OR操作是我现在唯一可以执行的操作）。

Answer 1

您可以使用HAVING：

SELECT  products.product_rec_id, COUNT(DISTINCT keywords.keyword) AS cnt
FROM  products
LEFT OUTER JOIN keywords_products ON
    products.product_rec_id = keywords_products.product_rec_id
LEFT OUTER JOIN keywords ON
    keywords_products.keyword_rec_id = keywords.keyword_rec_id
WHERE  (keywords.keyword LIKE '%scifi%' OR   keywords.keyword LIKE '%melee%')
GROUP BY products.product_rec_id
HAVING SUM(CASE WHEN keywords.keyword LIKE '%scifi%' THEN 1 ELSE 0 END)>0
  AND SUM(CASE WHEN keywords.keyword LIKE '%melee%' THEN 1 ELSE 0 END) > 0;

编辑：

要同时获取两者（如果在同一行中）：

SELECT  products.product_rec_id,keywords.keyword
FROM  products
LEFT OUTER JOIN keywords_products ON
    products.product_rec_id = keywords_products.product_rec_id
LEFT OUTER JOIN keywords ON
    keywords_products.keyword_rec_id = keywords.keyword_rec_id
WHERE  keywords.keyword LIKE '%scifi%melee%'
   OR keywords.keyword LIKE '%melee%scifi%';