MySQL多级父级选择/加入问题 [英] MySQL multi-level parent selection/join question

问题描述

我有一个希望很简单的MySQL查询问题，这在深夜使我难以理解.我正在尝试执行SELECT，该SELECT计算一组数据(订单)的实例数量，并通过在订单本身上方几层的父级中存在的值对这些实例进行分组.

I have a hopefully simple MySQL query question which is eluding me at late at night. I'm trying to do a SELECT which counts the number of instances of a set of data (orders) and groups those instances by a value which exists in a parent a couple levels above the order itself.

例如:

CREATE TABLE  `so_test`.`categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`product_group` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`products` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_group_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`orders` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

我想做的是附近的某事

选择计数(orders.id)，categoryId 从订单，类别WHERE (1,2,3)GROUP BY order.productId.productGroupId.categoryId

SELECT count(orders.id), categoryId FROM orders, categories WHERE orders.customer_id in (1,2,3) GROUP BY orders.productId.productGroupId.categoryId

假设类别1的产品有17个订单，类别2的产品有2个订单，类别3的产品有214个订单，我希望得到的是:

Assuming there are 17 orders for products in category 1, 2 orders for products in category 2, and 214 orders for category 3, what I'm hoping to get back is:

count(orders.id), categoryId
============================
      17              1
      2               2
      214             3

如果我要按product_id分组，那很好..但是两层向上的部分正在抛出我.

If I was trying to group by say product_id I'd be fine..but the two-levels-up portion is throwing me.

谢谢！

推荐答案

只需将它们加入在一起:

Just join them together:

select categoryid, count(orders.id)
from category c
left join product_group pg on pg.category_id = c.id
left join products on p on p.product_group_id = pg.id
left join orders o on o.product_id = p.id

对于没有订单的类别，count(orders.id)将返回0，而count(*)将返回一个或多个，这取决于产品组和产品的数量.

For categories without an order, count(orders.id) will return 0, while count(*) would return one or more, depending on the number of productgroups and products.

内部联接根本不会计算没有订单的类别.

An inner join would not count categories without orders at all.

这篇关于MySQL多级父级选择/加入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！