mysql - 查询厂商近一年营收情况

Question

我正在尝试复制客户的销售统计电子表格，但使用我们构建的架构。

电子表格如下所示:

Month  | Manufacturer A | Manufacturer B | Manufacturer C | ... etc

Jan-17 | £40,000        | £50,000        | £60,000        |
Feb-17 | £40,000        | £50,000        | £60,000        |
Mar-17 | £40,000        | £50,000        | £60,000        |
Apr-17 | £40,000        | £50,000        | £60,000        |
May-17 | £40,000        | £50,000        | £60,000        |
Jun-17 | £40,000        | £50,000        | £60,000        |
Jul-17 | £40,000        | £50,000        | £60,000        |
Aug-17 | £40,000        | £50,000        | £60,000        |
Sept-17| £40,000        | £50,000        | £60,000        |
Oct-17 | £40,000        | £50,000        | £60,000        |
Nov-17 | £40,000        | £50,000        | £60,000        |
Dec-17 | £40,000        | £50,000        | £60,000        |

我的架构如下所示:

Manufacturers:

id, name

Orders:

id, number, made (order date)

Items:

id, order_id, manufacturer_id, name, price

如何获取每个制造商每月的总计？

Answer 1

您需要“枢轴”功能，但 MySQL 本身不提供该功能。

这可能不是您想要的答案，但这是我有时使用的答案。如果您事先知道制造商的数量，则可以某种程度上模拟一个枢轴。

不过，它需要一个很长的查询，我有时会使用动态 SQL 来实现。

例如:

select
  year(o.order_date),
  month(o.order_date),
  sum(case when m.id = 'A' then i.price end) as 'Manufacturer A',
  sum(case when m.id = 'B' then i.price end) as 'Manufacturer B',
  ...
from orders o
join items i on i.order_id = o.id
join manufacturers m on m.id = i.manufacturer_id
group by year(o.order_date), month(o.order_date)