mysql - 获取最近 12 个月总和的数据

Question

我正在尝试从数据库生成报告，但遇到查询问题。所以:

我有三个表:

• orders (与 order_id, order_date, order_total, clients.client_id, shippings.ship_id )
• clients
• shippings

现在我要fetch orders.*, clients.*, shippings.*作为一排我可以通过 left join 实现，例如:

SELECT a.*, b.*, c.* 
FROM orders AS a
LEFT JOIN clients AS b ON a.client_id = b.client_id
LEFT JOIN shippings AS c ON a.ship_id = c.ship_id

但对于该行，我想包括 12 列(即 month01、month02、month03)，它们的总和为 orders.order_total。由 orders.order_date 分配的过去 12 个月.

我可以通过获取所有 [order, client, shipping] 行然后通过查询单独获取每一行来实现这一点:

SELECT 
    DATE_FORMAT(order_date, \'%m.%Y\') AS order_month,
    SUM(order_total) AS order_sum 
FROM 
    orders
WHERE 
    client_id = '.intval($clientID).'
    AND ship_id = '.intval($shipID).'
    AND order_date >= DATE_SUB(DATE_FORMAT(CURDATE(), \'%Y-%m-01\'), INTERVAL 12 MONTH)
GROUP BY 
    order_month;

但这给了我另外 12 行两列数据:order_month和 order_sum

有没有一种方法可以在一个查询中获取所有这些内容，并将月份包含在一行中？

示例数据(在此处创建过去 4 个月的数据):

http://sqlfiddle.com/#!9/139c6

Answer 1

我已经为您编写了 SQL，但我无法对其进行测试。所以我希望它有效。它比你想要的多一列 (mmonth):从开始开始的月份

SELECT DATE_FORMAT(order_date, \'%m.%Y\') AS order_month,
  SUM(order_total) AS order_sum,
  @mdiff := (PERIOD_DIFF( DATE(order_date) , DATE(NOW() - INTERVAL 12 MONTH )) / 100),
  SUM(IF(@mdiff =  0 , order_total, 0)) month_01,
  SUM(IF(@mdiff =  1 , order_total, 0)) month_02,
  SUM(IF(@mdiff =  2 , order_total, 0)) month_03,
  SUM(IF(@mdiff =  3 , order_total, 0)) month_04,
  SUM(IF(@mdiff =  4 , order_total, 0)) month_05,
  SUM(IF(@mdiff =  5 , order_total, 0)) month_06,
  SUM(IF(@mdiff =  6 , order_total, 0)) month_07,
  SUM(IF(@mdiff =  7 , order_total, 0)) month_08,
  SUM(IF(@mdiff =  8 , order_total, 0)) month_09,
  SUM(IF(@mdiff =  9 , order_total, 0)) month_10,
  SUM(IF(@mdiff = 10 , order_total, 0)) month_11,
  SUM(IF(@mdiff = 11 , order_total, 0)) month_12
FROM orders
WHERE client_id = '.intval($clientID).'
AND ship_id = '.intval($shipID).'
AND order_date >= DATE_SUB(DATE_FORMAT(CURDATE(), \'%Y-%m-01\'), INTERVAL 12 MONTH)
GROUP BY order_month;