mysql - 基于标签搜索书籍的 SQL 查询，具有正确的排序和标签优先级

Question

我已经学会了基本的 CRUD 操作，但现在我正在研究 JOINS 和更复杂的 MySQL 查询，我很挣扎。

这是我的表格:

CREATE TABLE books (
id INT NOT NULL auto_increment,
title VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tags (
id INT NOT NULL auto_increment,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE books_tags(
book_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (book_id, tag_id)
);

我目前拥有的(可能无法正常工作):

SELECT b.title
FROM books_tags bk1, books_tags bk2, books b
WHERE bk1.tag_id = TAG1 OR
      bk1.tag_id = TAG2 AND
      bk1.tag_id = bk2.tag_id AND
      b.id = bk2.book_id
GROUP BY bk2.book_id;

我想要的:

• 按标签搜索图书的查询
• 最匹配标签的书排在第一位
• 一些标签可以有不同的“重要性”(目前不是很重要，不确定是否可能)

Answer 1

Query 将按多个标签名称搜索书籍，并按最匹配的标签 降序排列。这意味着，具有更多匹配标签的图书将排在第一位。

SELECT
     b.id
    ,b.title
    ,count(1) AS tag_count
    ,group_concat(t.name) AS tags
FROM books b
    JOIN books_tags bt ON bt.book_id=b.id
    JOIN tags t ON t.id=bt.tag_id
WHERE t.name IN ('scary', 'creepy')
GROUP BY b.id, b.title
ORDER BY tag_count DESC;

示例数据:

INSERT INTO books VALUES(1, "Dracula");
INSERT INTO books VALUES(2, "Frankenstein");
INSERT INTO books VALUES(3, "Screenburn");

INSERT INTO tags VALUES (1, "scary");
INSERT INTO tags VALUES (2, "creepy");
INSERT INTO tags VALUES (3, "funny");

INSERT INTO books_tags VALUES (1, 1);
INSERT INTO books_tags VALUES (1, 2);
INSERT INTO books_tags VALUES (2, 1);
INSERT INTO books_tags VALUES (3, 3);

输出:

ID  TITLE           TAG_COUNT   TAGS
1   Dracula         2           scary,creepy
2   Frankenstein    1           scary

---

下面是实现标签优先级的完整示例:

CREATE TABLE books (
  id INT NOT NULL auto_increment,
  title VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE tags (
  id INT NOT NULL auto_increment,
  priority INT NOT NULL DEFAULT 1,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE books_tags(
  book_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (book_id, tag_id)
);

INSERT INTO books VALUES(1, "Dracula");
INSERT INTO books VALUES(2, "Frankenstein");
INSERT INTO books VALUES(3, "Screenburn");
INSERT INTO books VALUES(4, "Kitten story");

INSERT INTO tags VALUES (1, 1, "scary");
INSERT INTO tags VALUES (2, 3, "creepy");
INSERT INTO tags VALUES (3, 2, "funny");
INSERT INTO tags VALUES (4, 1, "useless");

INSERT INTO books_tags VALUES (1, 1),(1, 2);
INSERT INTO books_tags VALUES (2, 1);
INSERT INTO books_tags VALUES (3, 2),(3, 3);
INSERT INTO books_tags VALUES (4, 3),(4, 4);

选择:

SELECT
     b.id
    ,b.title
    ,group_concat(t.name) AS matching_tags
    ,count(1) AS tag_count
    ,sum(t.priority) AS priority
FROM books b
    JOIN books_tags bt ON bt.book_id=b.id
    JOIN tags t ON t.id=bt.tag_id
WHERE t.name IN ('scary', 'creepy', 'funny')
GROUP BY b.id, b.title
ORDER BY priority DESC;

输出:

ID  TITLE           MATCHING_TAGS   TAG_COUNT   PRIORITY
3   Screenburn      creepy,funny    2           5 (because 3+2)
1   Dracula         scary,creepy    2           4 (because 1+3)
4   Kitten story    funny           1           2 (because 2)
2   Frankenstein    scary           1           1 (because 1)