mysql多表聚合_mysql – 如何在查询多个表时准确使用聚合函数？

我比编写一个包含聚合函数的查询,查询多个表以及得到准确数字的结果并希望能得到一些帮助的时间更难.

类别表示例

该类别是我需要报告的内容：

|----|-----------|

| id | name |

|----|-----------|

| 1 | furniture |

| 2 | music |

| 3 | kitchen |

| 4 | adventure |

|----|-----------|

产品表示例

产品表示例：

|-----|----------------|-------------|

| id | name | category_id |

|-----|----------------|-------------|

| 101 | couch | 1 |

| 102 | chair | 1 |

| 103 | drum | 2 |

| 104 | flute | 2 |

| 105 | pot | 3 |

| 106 | pan | 3 |

| 107 | kitchen sink | 3 |

| 108 | unicorn saddle | 4 |

| 109 | unicorn shoes | 4 |

| 110 | horse shampoo | 4 |

|-----|----------------|-------------|

活动表示例

我们想要在活动表中找到的视图数据(按类别)：

|----|------------|-------|

| id | product_id | views |

|----|------------|-------|

| 1 | 101 | 1000 |

| 2 | 102 | 2000 |

| 3 | 103 | 3000 |

| 4 | 104 | 4000 |

| 5 | 105 | 5000 |

| 6 | 106 | 6000 |

| 7 | 107 | 7000 |

| 8 | 108 | 8000 |

| 9 | 109 | 9000 |

| 10 | 110 | 10000 |

|----|------------|-------|

销售表示例

我们要放置的销售表查询平均销售(再次按类别).请注意,vendor_id很重要,因为单个产品可以由多个供应商承载.我遗漏了供应商表,因为这个问题不需要(我们可以在后面的例子中使用供应商ID进行查询).

|----|------------|-----------|--------|

| id | product_id | vendor_id | amount |

|----|------------|-----------|--------|

| 1 | 101 | 1 | 1000 |

| 2 | 102 | 1 | 900 |

| 3 | 103 | 1 | 2000 |

| 4 | 105 | 1 | 3000 |

| 5 | 107 | 1 | 5000 |

| 6 | 101 | 2 | 600 |

| 7 | 103 | 2 | 7000 |

| 8 | 105 | 2 | 8000 |

| 9 | 107 | 2 | 1000 |

| 10 | 108 | 1 | 500 |

| 11 | 109 | 1 | 600 |

| 12 | 108 | 2 | 400 |

| 13 | 109 | 2 | 500 |

|----|------------|-----------|--------|

期望的输出

以下是所需的输出：

**请注意,某些供应商不携带某些产品,因此,意味着没有平均销售额.换句话说,产品表中的某些产品的销售表中没有记录(例如,没有供应商)携带马洗发水).出于这个原因,我想确保我使用的任何平均值或总和实际上是准确的.具体在这里,如果**.

|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|

| category | count_products | sum_views | average_sales | sum_views_where_sales_=>_1000 | sum_views_sales_<_1000>

|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|

| adventure | 3 | 27000 | 500 | 0 | 27000 |

| furniture | 2 | 3000 | 833 | 0 | 3000 |

| kitchen | 3 | 18000 | 3000 | 6000 | 12000 |

| music | 2 | 7000 | 5000 | 7000 | 0 |

|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|

当前查询状态

首先要准确了解产品和观点：

SELECT cat.name AS category,

count(distinct p.name) AS product,

sum(a.views) AS views

FROM

category AS cat,

product AS p,

activity AS a

WHERE

cat.id=p.category_id

AND

p.id=a.product_id

GROUP BY

category;

旁注：我希望不必在上面的查询中使用distinct.这里的任何想法都会很棒.

准确的结果按类别显示视图：

|-----------|---------|-------|

| category | product | views |

|-----------|---------|-------|

| Adventure | 3 | 27000 |

| Furniture | 2 | 3000 |

| Kitchen | 3 | 18000 |

| Music | 2 | 7000 |

|-----------|---------|-------|

在我开始加入其他表之前,一切看起来都很好：

SELECT cat.name AS category,

count(distinct p.name) AS product,

sum(a.views) AS views,

round(avg(s.amount)) AS sales_amount

FROM

category AS cat,

product AS p,

activity AS a,

sales AS s

WHERE

cat.id=p.category_id

AND

p.id=a.product_id

AND

p.id=s.product_id

AND

s.vendor_id=1

GROUP BY

category;

问题输出

|-----------|---------|-------|------------------|

| category | product | views | avg_sales_amount |

|-----------|---------|-------|------------------|

| Adventure | 2 | 17000 | 550 |

| Furniture | 2 | 3000 | 950 |

| Kitchen | 2 | 12000 | 4000 |

| Music | 1 | 3000 | 2000 |

|-----------|---------|-------|------------------|

您可以注意到,当我开始通过vendor_id查询以获得平均销售额时,我将从所需的输出中获得更多.具体而言,产品列不再产生正确数量的产品,因为并非所有供应商都携带所有相同的产品,使得s.vendor_id = 1过滤器变得困难.我必须使用它来按供应商过滤这些报告,同时仍然在视图字段上获得准确的总和.

我已经使用LEFT JOIN尝试了上述查询,但最终仍然得到了无差错的结果,并且不确定需要发生什么,可能是某种子查询？