mysql 查询某个字段同时拥有两个值

时间:2025-02-16 10:59:07

创建表

CREATE TABLE `tags` (
  `tid` int(11) DEFAULT NULL,
  `bookid` int(11) DEFAULT NULL,
  `content` char(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tags
-- ----------------------------
INSERT INTO `tags` VALUES ('10', '5', 'php');
INSERT INTO `tags` VALUES ('11', '5', 'web');
INSERT INTO `tags` VALUES ('12', '6', 'web');
INSERT INTO `tags` VALUES ('13', '6', 'ruby');
INSERT INTO `tags` VALUES ('14', '7', 'database');
INSERT INTO `tags` VALUES ('15', '8', 'ruby');
INSERT INTO `tags` VALUES ('16', '8', 'server');

查询content内容同时为php和web的bookid

SELECT
    bookid
FROM
    tags
WHERE
    content IN ('php', 'web')
GROUP BY
    bookid
HAVING
    count(bookid) > 1

or

SELECT
    t1.bookid
FROM
    tags AS t1
INNER JOIN tags AS t2 ON t1.bookid = t2.bookid
WHERE
    t1.content = 'php'
AND t2.content = 'web'