mysql - 如何在3表中获得vistors_sum和reviews_count？

如何在3表中获得vistors_sum和reviews_count？
看到下面的代码，如何在一个sql中获得我的结果？

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `a`
-- ----------------------------
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `products_id` int(11) NOT NULL,
  `products_name` varchar(255) default NULL,
  PRIMARY KEY  (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', 'jimmy');
INSERT INTO `a` VALUES ('2', 'tina');
INSERT INTO `a` VALUES ('3', 'emma');



SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `b`
-- ----------------------------
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(11) NOT NULL auto_increment,
  `products_id` int(11) NOT NULL,
  `vistors` int(11) NOT NULL,
  `date` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of b
-- ----------------------------
INSERT INTO `b` VALUES ('1', '1', '1', '2013-11-13');
INSERT INTO `b` VALUES ('2', '1', '2', '2013-11-04');
INSERT INTO `b` VALUES ('3', '2', '1', '2013-11-13');
INSERT INTO `b` VALUES ('4', '2', '3', '2013-11-13');


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `c`
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `id` int(11) NOT NULL auto_increment,
  `products_id` int(11) NOT NULL,
  `review_content` varchar(255) default NULL,
  `date` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of c
-- ----------------------------
INSERT INTO `c` VALUES ('1', '1', 'hello', '2013-11-13');
INSERT INTO `c` VALUES ('2', '1', 'world', '2013-11-13');
INSERT INTO `c` VALUES ('3', '2', 'good', '2013-11-12');
INSERT INTO `c` VALUES ('4', '3', 'boy', '2013-11-13');

下面的代码可以执行此操作，但日期条件在子子级临时表中。这使sql不灵活（如果我想在任何时候都不能查询的话2013-11-13）

select
    a.products_id,
    a.products_name,
    b.vistors_sum,
    c.reviews_count
from
    a
    left join
    (
        select
            b.products_id,
            b.date,
            sum(b.vistors) as vistors_sum
        from b
        where b.date = '2013-11-13'
        group by b.products_id
    ) as b on a.products_id = b.products_id
    left join
    (
        select
            c.products_id,
            count(c.products_id) as reviews_count
        from c
        where c.date = '2013-11-13'
        group by c.products_id
    ) as c on a.products_id = c.products_id

最佳答案

SQLFiddle demo

select a.products_id,
       products_name,
       COALESCE(b.sum_visitors,0) as sum_visitors,
       COALESCE(c.count_comments,0) as count_comments

from a
left join
( SELECT products_id,sum(vistors) as sum_visitors
  FROM b
  WHERE date='2013-11-13'
  GROUP BY products_id
) as b

on (a.products_id=b.products_id)

left join
(
  SELECT products_id,count(*) as count_comments
  FROM c
  WHERE date='2013-11-13'
  GROUP BY products_id
) as c

on (a.products_id=c.products_id)