mysql - 如何最好地组合和优化这两个查询？

Question

这是我的主要查询，它将线程信息作为一行拉入，它缺少投票数，目前我正在通过第二个查询将其拉入。

SELECT   Group_concat(t.tag_name) AS `tags`,
         `p`.`thread_id`,
         `p`.`thread_name`,
         `p`.`thread_description`,
         `p`.`thread_owner_id`,
         `p`.`thread_view_count`,
         `p`.`thread_reply_count`,
         `p`.`thread_comment_count`,
         `p`.`thread_favorite_count`,
         `p`.`thread_creation_date`,
         `p`.`thread_type_id`,
         `p`.`thread_edited_date`,
         `u`.*,
         `x`.*,
         `t`.*

FROM     `shoop_posts` AS `p`
         INNER JOIN `shoop_users` AS `u`
           ON u.user_id = p.thread_owner_id
         LEFT JOIN `shoop_tags_map` AS `x`
           ON x.thread_id = p.thread_id
         LEFT JOIN `shoop_tags` AS `t`
           ON t.tag_id = x.tag_id


WHERE    (p.thread_id = '1')
GROUP BY `p`.`thread_id` 

我的第二个查询提取每个线程的投票数:

SELECT Sum(vote_value)
FROM   shoop_votes
       INNER JOIN shoop_vote_codes
         ON shoop_votes.vote_type = shoop_vote_codes.vote_type
WHERE  thread_id = 1
       AND shoop_votes.vote_type = 3
        OR shoop_votes.vote_type = 2 

投票类型为 2 表示赞成，3 表示反对。如果您需要的话，这里是架构以及一些示例数据:

CREATE TABLE `shoop_posts` (

  `thread_id` int(11) unsigned NOT NULL auto_increment,

  `thread_name` text,

  `thread_description` text,

  `thread_parent_id` int(11) default NULL,

  `thread_owner_id` int(11) default NULL,

  `thread_view_count` int(11) default NULL,

  `thread_reply_count` int(11) default NULL,

  `thread_comment_count` int(11) default NULL,

  `thread_favorite_count` int(11) default NULL,

  `thread_creation_date` timestamp NULL default NULL,

  `thread_type_id` int(11) default NULL,

  `thread_edited_date` timestamp NULL default NULL,

  PRIMARY KEY  (`thread_id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of shoop_posts

-- ----------------------------

INSERT INTO `shoop_posts` VALUES ('1', 'Shoop that', '\r\n<img class=\"image-shoop\" src=\"\">\r\n\r\n<p>test:<br>\r\n\r\n\r\n</p>', null, '2', '217', '0', '0', '0', '2010-01-10 02:06:25', '1', null);



-- ----------------------------

-- Table structure for `shoop_tags`

-- ----------------------------


CREATE TABLE `shoop_tags` (

  `tag_id` int(11) NOT NULL auto_increment,

  `tag_name` varchar(11) default NULL,

  PRIMARY KEY  (`tag_id`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of shoop_tags

-- ----------------------------

INSERT INTO `shoop_tags` VALUES ('1', 'mma');

INSERT INTO `shoop_tags` VALUES ('2', 'strikeforce');

INSERT INTO `shoop_tags` VALUES ('3', 'ufc');



-- ----------------------------

-- Table structure for `shoop_tags_map`

-- ----------------------------

DROP TABLE IF EXISTS `shoop_tags_map`;

CREATE TABLE `shoop_tags_map` (

  `map_id` int(11) NOT NULL auto_increment,

  `tag_id` int(11) default NULL,

  `thread_id` int(11) default NULL,

  PRIMARY KEY  (`map_id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of shoop_tags_map

-- ----------------------------

INSERT INTO `shoop_tags_map` VALUES ('1', '1', '1');

INSERT INTO `shoop_tags_map` VALUES ('2', '2', '2');

INSERT INTO `shoop_tags_map` VALUES ('3', '1', '2');

INSERT INTO `shoop_tags_map` VALUES ('4', '3', '1');

INSERT INTO `shoop_tags_map` VALUES ('5', '3', '2');




-- ----------------------------

-- Table structure for `shoop_vote_codes`

-- ----------------------------


CREATE TABLE `shoop_vote_codes` (

  `vote_type` smallint(1) NOT NULL default '0',

  `vote_value` smallint(2) default NULL,

  PRIMARY KEY  (`vote_type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of shoop_vote_codes

-- ----------------------------

INSERT INTO `shoop_vote_codes` VALUES ('2', '1');

INSERT INTO `shoop_vote_codes` VALUES ('3', '-1');



-- ----------------------------

-- Table structure for `shoop_votes`

-- ----------------------------

DROP TABLE IF EXISTS `shoop_votes`;

CREATE TABLE `shoop_votes` (

  `thread_id` int(11) NOT NULL default '0',

  `user_id` int(11) NOT NULL default '0',

  `vote_type` smallint(1) NOT NULL default '0',

  PRIMARY KEY  (`thread_id`,`user_id`,`vote_type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of shoop_votes

-- ----------------------------

INSERT INTO `shoop_votes` VALUES ('1', '1', '2');

INSERT INTO `shoop_votes` VALUES ('1', '2', '2');

INSERT INTO `shoop_votes` VALUES ('1', '3', '3');

Answer 1

如果我理解正确，只需使用子查询即可完成您的任务:

SELECT   Group_concat(t.tag_name) AS `tags`,
         `p`.`thread_id`,
         `p`.`thread_name`,
         `p`.`thread_description`,
         `p`.`thread_owner_id`,
         `p`.`thread_view_count`,
         `p`.`thread_reply_count`,
         `p`.`thread_comment_count`,
         `p`.`thread_favorite_count`,
         `p`.`thread_creation_date`,
         `p`.`thread_type_id`,
         `p`.`thread_edited_date`,
         `u`.*,
         `x`.*,
         `t`.*,
         `v`.VoteTotal

FROM     `shoop_posts` AS `p`
         INNER JOIN `shoop_users` AS `u`
           ON u.user_id = p.thread_owner_id
         LEFT JOIN `shoop_tags_map` AS `x`
           ON x.thread_id = p.thread_id
         LEFT JOIN `shoop_tags` AS `t`
           ON t.tag_id = x.tag_id
         LEFT JOIN (SELECT thread_id, Sum(vote_value) as VoteTotal
                    FROM   shoop_votes
                       INNER JOIN shoop_vote_codes
                         ON shoop_votes.vote_type = shoop_vote_codes.vote_type
                    WHERE  shoop_votes.vote_type = 3
                           OR shoop_votes.vote_type = 2
                    GROUP BY thread_id) as `v`
           ON p.thread_id = v.thread_id
WHERE    (p.thread_id = '1')
GROUP BY `p`.`thread_id` 

如果您省略 where last where p.thread_id 子句，您也可以获取所有线程。