我有3个表:
CREATE TABLE IF NOT EXISTS `Articles` (
`id` int(11) NOT NULL DEFAULT '',
...
...
PRIMARY KEY (`id_article`)
) ENGINE=InnoDB ;
Persons_Articles:
CREATE TABLE IF NOT EXISTS `Persons_Articles` (
`id_article` int(11) NOT NULL,
`id_person` int(11) UNSIGNED NOT NULL,
`role` TINYINT(4) UNSIGNED,
PRIMARY KEY (`id_article`,`id_person`,`role`)
) ENGINE=InnoDB ;
人:
CREATE TABLE IF NOT EXISTS `Persons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(64) DEFAULT NULL,
`lastname` varchar(64) NOT NULL,
`fullname` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
INSERT INTO Articles VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO Persons_Articles VALUES (1, 1, 1);
INSERT INTO Persons_Articles VALUES (1, 2, 2);
INSERT INTO Persons VALUES (1, 'Mick', 'Jagger', 'Mick Jagger');
INSERT INTO Persons VALUES (2, 'Keith', 'Richards', 'Keith Richards');
我寻找一个可以返回(fullname = 'Mick Jagger' AND role = 1)和(fullname = 'Keith Richards' AND role = 2)的文章的查询
有什么想法吗?
尝试以下操作:
SELECT * FROM Persons P
INNER JOIN Persons_Articles PA ON PA.id_person = P.id
INNER JOIN Articles A ON A.id = PA.id_article
WHERE (P.fullname = 'Mick Jagger' AND PA.role = 1) OR (P.fullname = 'Keith Richards' AND PA.role = 2)
这假设您不能有一个文章的副本(名称和角色):
SELECT a.*
FROM articles a
JOIN persons_articles pa
ON pa.id_article = a.id
JOIN persons p
ON pa.id_person = p.id
AND ((p.fullname = 'Mick Jagger' AND pa.role = 1) OR (p.fullname = 'Keith Richards' AND pa.role = 2))
GROUP BY a.id
HAVING count(*) = 2;
作为旁注,我永远不会在数据库中存储全名。它会导致更多的UPDATE
s和INSERT
s的工作,如果你不小心,你可能会得到不匹配的名称。虽然这是稍微多一点的工作,你总是可以CONCAT()
的名称,如果需要的话。