mysql - 获取每个项目每年相似项目的数量

Question

请看一下这个fiddle :

我有这个查询来获取每个商品的价格相对于其所属类别的平均价格的百分比差异:

SELECT name, product.category, ((price - avg_price)/avg_price)*100 as price_difference
FROM product
JOIN (
  SELECT category, AVG(price) as avg_price
  FROM product
  GROUP BY category
) t
ON t.category = product.category

现在我想进一步添加另一个查询来按年份统计同一类别中的产品数量

原表:

ID  name    category      year
1     A     Outdoor       2010
2     B     Doll          2009
3     C     Doll          2009
4     D     Outdoor       2010
5     E     Brainteaser   2010
6     F     Brainteaser   2009
7     G     Brainteaser   2009
8     H     Brainteaser   2009

结果应该是这样的:

name CATEGORY      DIFFERENCE   similar_products_of_the_same_year
A   Outdoor             -72%              2         
B   Doll                 29%              2
C   Doll                -29%              2
D   Outdoor               9%              2
E   Brainteaser         -88%              1
F   Brainteaser         134%              3
G   Brainteaser          65%              3
H   Brainteaser         -47%              3

我只能进行如下所示的特定查询，但我需要获取每个产品的记录。

SELECT COUNT(year) AS similar_items_of_the_year from `product` where year in (
  SELECT year
  FROM product
  where name = "A"
) 

有人可以告诉我如何得到这个结果吗？

Answer 1

SELECT p.name
     , p.year
     , p.category
     , ((p.price - avg_price)/avg_price)*100 price_difference
     , ttl
  FROM product p
  JOIN 
     ( SELECT category
            , AVG(price) avg_price
         FROM product
        GROUP 
           BY category
     ) t
    ON t.category = p.category
  JOIN 
     ( SELECT category,year, COUNT(*) ttl FROM product GROUP BY category,year ) n
    ON n.year = p.year 
   AND n.category = p.category;

http://sqlfiddle.com/#!2/f182e/14

如果我的结果与您的不同，我认为这是因为我不明白您是否认为一行与其自身相似。如果这样做，只需从 ttl 中减去 1。