mysql - 我怎样才能让 mysql 与标签搜索一起获得平均评分？

Question

我正在努力思考一个复杂的 mysql 语句(反正对我来说很复杂!)。基本上，我需要从产品表中返回所有产品的列表具有额外的返回值(它们各自的星级(评级表)，其中必须根据该产品所有评级的总和的平均值来计算)。

sql 语句还必须包括根据以下条件过滤产品的能力多个“标签”词，例如搜索所有链接的产品(通过product_tags表到tags表)构造时指定的词sql语句。所以，如果我需要检索带有“红色”和“白色”标签的产品，结果将返回产品 1 和 3 及其各自的平均评分。

下面是示例表的 sql 转储。

DROP TABLE IF EXISTS `product_tags`;
DROP TABLE IF EXISTS `rating`;
DROP TABLE IF EXISTS `tags`;
DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `products` (`product_id`, `product_name`, `date_added`) VALUES
(1, 'first item', '2011-05-26 21:56:06'),
(2, 'second item', '2011-05-26 21:56:06'),
(3, 'third item', '2011-05-26 21:56:06');


CREATE TABLE IF NOT EXISTS `product_tags` (
  `product_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  KEY `product_id` (`product_id`),
  KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `product_tags` (`product_id`, `tag_id`) VALUES
(1, 4),
(1, 1),
(1, 8),
(2, 3),
(2, 9),
(3, 8),
(3, 7),
(1, 6),
(2, 5),
(3, 2),
(3, 10);

CREATE TABLE IF NOT EXISTS `rating` (
  `product_id` int(11) NOT NULL,
  `rating` float NOT NULL,
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `rating` (`product_id`, `rating`) VALUES
(1, 5),
(1, 0),
(2, 3),
(2, 4.5),
(1, 2),
(2, 4);

CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(50) NOT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES
(1, 'red'),
(2, 'green'),
(3, 'yellow'),
(4, 'cyan'),
(5, 'blue'),
(6, 'pink'),
(7, 'purple'),
(8, 'grey'),
(9, 'black'),
(10, 'white');

ALTER TABLE `product_tags`
  ADD CONSTRAINT `product_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `product_tags_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product_tags` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `rating`
  ADD CONSTRAINT `rating_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

SELECT  
    p.product_id
  , p.product_name
  , p.date_added
  , ( SELECT AVG(r.rating) 
      FROM rating r
      WHERE r.product_id = p.product_id
    )
    AS avg_rating 
FROM
  products p
    JOIN
  product_tags pt
      ON pt.product_id = p.product_id
    JOIN
  tags t
      ON t.tag_id = pt.tag_id
WHERE
    t.tag_name IN ('red','white')
GROUP BY
    p.product_id

---

作为旁注，表名最好使用单数形式。 product、tag、product_tag(如 rating 已经是单数)而不是复数:产品等