MySQL查询特定的年份和类别

I think it's hard to find my Edit on my older post so I make a new thead:

我觉得很难在我的旧帖子上找到我的编辑所以我创建了一个新的thead:

After solving this so fast I tried to play a bit around to train on SQL a bit. Now I try to make the same as earlier, just instead of countries/regions, I want 'categories' in there(post before was about making a query where I can see the netamount of sales for the US and rest of the World for the years 2007 and 2008 in the database, now i try to make it with year and categories which begin with the letter 'A') As I tried to make such a query I totally lost myself in it O.o I think I need to show the entities beforehand: (Over the line = tablename)

在解决了这么快之后我试着玩一下来训练一下SQL。现在我试着像以前一样做,而不是国家/地区,我想要“类别”(之前的帖子是关于进行查询,我可以看到美国和世界其他地区的销售净额) 2007年和2008年在数据库中,现在我尝试用年份和类别开头的字母'A'开头。当我试图做出这样的查询时,我完全迷失了自己,我认为我需要展示实体事先:(在线= tablename)

• Categories

  ---

• Category

• Categoryname

• Products

  ---

• ProdID

• Category
• Title
• Actor

• Price

• orderlines

  ---

• orderid

• orderlineid

• Prodid

• orders

  ---

• orderid

• orderdate
• customerid
• netamount

my query so far: (absolute no sense behind it :/ )

到目前为止我的查询:(绝对没有任何意义:/)

SELECT year(orderdate) AS YEAR,
       categoryname,
       sum(custamot)Totsales FROM
  (SELECT o.orders, sum(netamount) custamot, c.categoryname
   FROM products o
   JOIN categories c ON o.category = c.category
   WHERE categoryname LIKE 'A%'
   GROUP BY c.categoryname) iv
GROUP BY YEAR(orderdate);

1 个解决方案

#1

You shouldn't need a subquery at all. Just join all the tables you need and write a suitable group by clause for your aggregation. You'll end up with something like:

您根本不需要子查询。只需加入您需要的所有表,并为您的聚合编写一个合适的group by子句。你会得到类似的东西:

SELECT YEAR(o.orderdate) AS YEAR,
       c.Categoryname,
       SUM(o.netamount) AS Totsales
FROM orders o
INNER JOIN orderlines ol ON ol.orderid = o.orderid
INNER JOIN Products p ON p.ProdID = ol.Prodid
INNER JOIN categories c ON c.Category = p.Category
WHERE c.Categoryname LIKE 'A%'
GROUP BY c.Categoryname, YEAR(o.orderdate);