php - 如何比较逗号分隔值和两个不同的ids-php mysql

Question

所显示的人物照片只是虚假数据。 这是我的表结构

id |作者    |共同作者

1    |罗尼 |维奈、戈帕尔、阿伦
2    | Razor |    |卡西克、韦伯斯特、明
3    |塔兹        |伯爵、卡西克、维奈
4    |男爵 |戈帕尔、阿伦、桑吉夫

现在我想选择两位作者说 ronnie 和 baron，所以现在应该比较他们的合著者。在这种情况下，匹配项将为 gopal、arun，因此输出应为 gopal 和 arun。

如何在 mysql 查询中执行此操作。请帮忙。

Answer 1

这是为您的项目建议的结构。在您的案例中，它可能是一个 articles 表，而不是 books 或与作者和共同作者相关的任何其他内容。这样，以后查询数据就会容易得多:

SQL Fiddle

MySQL 5.5.32 架构设置:

CREATE TABLE books
    (`id` int, `title` varchar(5));

INSERT INTO books
    (`id`, `title`)
VALUES
    (1, 'book1'),
    (2, 'book2'),
    (3, 'book3'),
    (4, 'book4');

CREATE TABLE authors
    (`id` int, `name` varchar(8));

INSERT INTO authors
    (`id`, `name`)
VALUES
    (1, 'Ronnie'),
    (2, 'Razor'),
    (3, 'Taz'),
    (4, 'Baron'),
    (5, 'vinay'),
    (6, 'karthick'),
    (7, 'earl'),
    (8, 'gopal'),
    (9, 'webster'),
    (10, 'karthick'),
    (11, 'arun'),
    (12, 'ming'),
    (13, 'sanjeev');

CREATE TABLE type
    (`id` int, `type` varchar(9));

INSERT INTO type
    (`id`, `type`)
VALUES
    (1, 'author'),
    (2, 'co-author');

CREATE TABLE wrote
    (`id` int, `book_id` int, `author_id` int, `type_id` int);

INSERT INTO wrote
    (`id`, `book_id`, `author_id`, `type_id`)
VALUES
    (1, 1, 1, 1),
    (2, 2, 2, 1),
    (3, 3, 3, 1),
    (4, 4, 4, 1),
    (5, 1, 5, 2),
    (6, 2, 6, 2),
    (7, 3, 7, 2),
    (8, 4, 8, 2),
    (9, 1, 8, 2),
    (10, 2, 9, 2),
    (11, 3, 10, 2),
    (12, 4, 11, 2),
    (13, 1, 11, 2),
    (14, 2, 12, 2),
    (15, 3, 5, 2),
    (16, 4, 13, 2);

这是您的实际数据:

查询 1:

SELECT b.id, a.name as author, group_concat(co.name) as co_authors
FROM books b
INNER JOIN wrote wa ON wa.book_id = b.id AND wa.type_id = 1
INNER JOIN authors a ON wa.author_id = a.id 
INNER JOIN wrote wco ON wco.book_id = b.id AND wco.type_id = 2
INNER JOIN authors co ON wco.author_id = co.id
GROUP BY b.title, a.name

<强> Results :

| ID | AUTHOR |            CO_AUTHORS |
|----|--------|-----------------------|
|  1 | Ronnie |      arun,vinay,gopal |
|  2 |  Razor | webster,ming,karthick |
|  3 |    Taz |   earl,karthick,vinay |
|  4 |  Baron |    sanjeev,gopal,arun |

这就是您正在尝试做的事情。

注意 (1,4) 中的 AND a.id;，其中 id 1 是 Ronnie 和 id 4 是男爵:

查询 2:

SELECT co.name as coauthor
FROM books b
INNER JOIN wrote wa ON wa.book_id = b.id AND wa.type_id = 1
INNER JOIN authors a ON wa.author_id = a.id AND a.id in (1,4)
INNER JOIN wrote wco ON wco.book_id = b.id AND wco.type_id = 2
INNER JOIN authors co ON wco.author_id = co.id 
GROUP BY co.name
HAVING count(co.name) > 1

<强> Results :

| COAUTHOR |
|----------|
|     arun |
|    gopal |