sql - 在两个逗号分隔字段之间搜索

Question

假设这两个表:

博客

id title      keywords
1  Hello      hello,world,test
2  Google     search, email, security
3  Microsoft  clippy, collaboration

类别

id keywords
1  test, world, clippy

如何搜索具有一个或多个与类别行中的关键字匹配的关键字的博客行？

Answer 1

简单地说:如果你想执行该操作，你就不会那样存储数据。您标准化数据，并使用标准化数据进行搜索:

CREATE TABLE Blog
(
    id      INTEGER NOT NULL PRIMARY KEY,
    title   VARCHAR(30) NOT NULL
);

INSERT INTO Blog(id, title) VALUES(1, 'Hello');
INSERT INTO Blog(id, title) VALUES(2, 'Google');
INSERT INTO Blog(id, title) VALUES(3, 'Microsoft');

CREATE TABLE BlogKeywords
(
    id      INTEGER NOT NULL,
    keyword VARCHAR(20) NOT NULL,
    PRIMARY KEY(keyword, id)
);

INSERT INTO BlogKeywords(id, keyword) VALUES(1, 'hello');
INSERT INTO BlogKeywords(id, keyword) VALUES(1, 'world');
INSERT INTO BlogKeywords(id, keyword) VALUES(1, 'test');
INSERT INTO BlogKeywords(id, keyword) VALUES(2, 'search');
INSERT INTO BlogKeywords(id, keyword) VALUES(2, 'email');
INSERT INTO BlogKeywords(id, keyword) VALUES(2, 'security');
INSERT INTO BlogKeywords(id, keyword) VALUES(3, 'clippy');
INSERT INTO BlogKeywords(id, keyword) VALUES(3, 'collaboration');

CREATE TABLE Category
(
    id      INTEGER NOT NULL,
    keyword VARCHAR(20) NOT NULL,
    PRIMARY KEY(id, keyword)
);

INSERT INTO Category(id, keyword) VALUES(1, 'test');
INSERT INTO Category(id, keyword) VALUES(1, 'world');
INSERT INTO Category(id, keyword) VALUES(1, 'clippy');

现在您可以使用简单的联接轻松搜索:

SELECT DISTINCT b.id AS BlogID, b.Title, c.id AS CategoryID
  FROM Blog AS b
  JOIN BlogKeywords AS K ON b.id      = k.id
  JOIN Category     AS C ON k.keyword = c.keyword;

如果有多个类别，目前尚不完全清楚您想要查看什么。