sql – 选择具有匹配标记的所有项目

SQL代码复制/粘贴和乱七八糟.

CREATE TABLE IF NOT EXISTS `projects` (
  `num` int(2) NOT NULL auto_increment,`title` varchar(30) NOT NULL,PRIMARY KEY  (`num`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `projects` (`num`,`title`) VALUES(1,'project 1'),(2,'project 2'),(3,'project 3'),(4,'project 4');


CREATE TABLE IF NOT EXISTS `projects_to_tags` (
  `project_id` int(2) NOT NULL,`tag_id` int(2) NOT NULL,KEY `project_id` (`project_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `projects_to_tags` (`project_id`,`tag_id`) VALUES(1,1),(1,2),3),3);


CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(2) NOT NULL auto_increment,`tag` varchar(30) NOT NULL,PRIMARY KEY  (`tag_id`),UNIQUE KEY `tag` (`tag`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `tags` (`tag_id`,`tag`) VALUES(1,'tag a'),'tag b'),'tag c');

解决方法

使用IN

SELECT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
 WHERE pt.tag_id IN (SELECT x.tag_id
                       FROM PROJECTS_TO_TAGS x
                      WHERE x.project_id = 4)

使用EXISTS

SELECT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
 WHERE EXISTS (SELECT NULL
                 FROM PROJECTS_TO_TAGS x
                WHERE x.project_id = 4
                  AND x.tag_id = pt.tag_id)

使用JOINS(这是最有效的！)

DISTINCT是必要的,因为JOIN会冒结果集中出现重复数据的风险……

SELECT DISTINCT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
  JOIN PROJECTS_TO_TAGS x ON x.tag_id = pt.tag_id
                         AND x.project_id = 4

（编辑：泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!