我提出了以下方案:

  CREATE TABLE products
  (
    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    quantity INT(10) UNSIGNED NOT NULL,
    purchase_price DECIMAL(8,2) NOT NULL,
    sell_price DECIMAL(8,2) NOT NULL,
    provider VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    PRIMARY KEY (id)
  );

  # payment methods = {
  #   "0": "CASH",
  #   "1": "CREDIT CARD",
  #   ...
  # }
  CREATE TABLE orders
  (
    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    product_id INT(10) UNSIGNED NOT NULL,
    quantity INT(10) UNSIGNED NOT NULL,
    payment_method INT(11) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (product_id) REFERENCES products(id)
  );

  # status = {
  #   "0": "PENDING"
  #   "1": "PAID"
  # }
  CREATE TABLE invoices
  (
    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    price INT(10) UNSIGNED NOT NULL,
    status INT(10) UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL,
    PRIMARY KEY (id)
  );

  # payment methods = {
  #   "0": 'CASH',
  #   "1": 'CREDIT CARD',
  #   ...
  # }
  CREATE TABLE bills
  (
    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    payment_method INT(10) UNSIGNED NOT NULL DEFAULT 0,
    price DECIMAL(8,2) NOT NULL,
    created_at TIMESTAMP NULL,
    PRIMARY KEY (id)
  );


然后通过以下查询选择余额:

SELECT ((orders + invoices) - bills) as balance
FROM
(
    SELECT SUM(p.sell_price * o.quantity) as orders
    FROM orders o
    JOIN products p
    ON o.product_id = p.id
) orders,
(
    SELECT SUM(price) as invoices
    FROM invoices
    WHERE status = 1
) invoices,
(
    SELECT SUM(price) as bills
    FROM bills
) bills;


它可以正常工作并返回正确的余额,但是我想使用Morris.js创建图表,并且需要对其进行更改以在给定的时间段内以以下格式返回每日或每月余额:

每日(2017-02-27至2017-03-01)

balance | created_at
--------------------------
600.00  | 2017-03-01
50.00   | 2017-02-28
450.00  | 2017-02-27


以及每月一次(2017-01至2017-03)

balance | created_at
--------------------------
200.00  | 2017-03
250.00  | 2017-02
350.00  | 2017-01


我需要在架构或查询中进行哪些更改才能以这种方式返回结果?

http://sqlfiddle.com/#!9/2289a9/2

任何提示都欢迎。提前致谢

最佳答案

在选择列表中包括created_at日期,在每个查询中包括GROUP BY子句。

放弃旧的逗号运算符进行联接操作,并用LEFT JOIN代替它。

要返回没有订单(或付款,发票)的日期,我们需要一个单独的行源,该行源保证可以返回日期值。例如,我们可以使用内联视图:

SELECT d.created_dt
  FROM (  SELECT '2017-02-27' + INTERVAL 0 DAY AS created_dt
          UNION ALL SELECT '2017-02-28'
          UNION ALL SELECT '2017-03-01'
       ) d
 ORDER BY d.created_dt


内联视图只是一个选择。如果我们有一个calendar表,其中包含我们感兴趣的三个日期的行,则可以使用该表。重要的是,我们有一个查询,该查询可以确保准确返回三行,其中包含我们要返回的不同的created_at日期值。

一旦有了,我们可以添加一个LEFT JOIN以获得该日期的“账单”值。

SELECT d.created_dt
     , b.bills
  FROM (  SELECT '2017-02-27' + INTERVAL 0 DAY AS created_dt
          UNION ALL SELECT '2017-02-28'
          UNION ALL SELECT '2017-03-01'
       ) d
  LEFT
  JOIN ( SELECT DATE(bills.created_at) AS created_dt
              , SUM(bills.price)       AS bills
           FROM bills
          WHERE bills.created_at >= '2017-02-27'
            AND bills.created_at <  '2017-03-01' + INTERVAL 1 DAY
          GROUP BY DATE(bills.created_at)
       ) b
    ON b.created_dt = d.created_dt
 ORDER BY d.created_dt


扩展它以添加另一个LEFT JOIN以获取发票

SELECT d.created_dt
     , i.invoices
     , b.bills
  FROM (  SELECT '2017-02-27' + INTERVAL 0 DAY AS created_dt
          UNION ALL SELECT '2017-02-28'
          UNION ALL SELECT '2017-03-01'
       ) d
  LEFT
  JOIN ( SELECT DATE(bills.created_at) AS created_dt
              , SUM(bills.price)       AS  bills
           FROM bills
          WHERE bills.created_at >= '2017-02-27'
            AND bills.created_at <  '2017-03-01' + INTERVAL 1 DAY
          GROUP BY DATE(bills.created_at)
       ) b
    ON b.created_dt = d.created_dt
  LEFT
  JOIN ( SELECT DATE(invoices.created_at) AS created_dt
              , SUM(invoices.price)       AS invoices
           FROM invoices
          WHERE invoices.status = 1
            AND invoices.created_at >= '2017-02-27'
            AND invoices.created_at <  '2017-03-01' + INTERVAL 1 DAY
          GROUP BY DATE(invoices.created_at)
       ) i
    ON i.created_dt = d.created_dt
 ORDER BY d.created_dt


类似地,我们可以向左联动视图到另一个内联视图,该视图返回按DATE(created_at)分组的总计orders

重要的是,内联视图返回不同的created_dt值,即每个日期值的一行。

请注意,对于开发,测试和调试,我们可以独立地仅执行内联视图查询。

当没有从LEFT JOIN返回匹配行时,例如,由于该日期没有发票,因此没有从i返回匹配行,查询将为表达式i.invoices返回NULL。要将NULL替换为零,我们可以使用IFNULL函数,或者使用更符合ANSI标准的COALESCE函数。例如:

SELECT d.created_dt
     , IFNULL(i.invoices,0) AS invoices
     , COALESCE(b.bills,0)  AS bills
  FROM ...




要每月获取结果,我们需要一个日历查询,该查询每月返回一行。假设我们要返回一个DATE值,该值是该月的第一天。例如:

SELECT d.created_month
  FROM (  SELECT '2017-02-01' + INTERVAL 0 DAY AS created_month
          UNION ALL SELECT '2017-03-01'
       ) d
 ORDER BY d.created_month


内联视图查询将需要为GROUP BY created_month,因此它们将为每个月值返回一个值。我的偏好是使用DATE_FORMAT函数返回从created_at派生的月份的第一天。但是还有其他方法可以做到这一点。目标是为“ 2017-02-01”返回一行,为“ 2017-03-01”返回一行。请注意,created_at上的日期范围从“ 2017-02-01”到(但不包括)“ 2017-04-01”,因此我们获得了整个月的总数。

           ( SELECT DATE_FORMAT(bills.created_at,'%Y-%m-01') AS created_month
                  , SUM(bills.price)                         AS  bills
               FROM bills
              WHERE bills.created_at >= '2017-02-01'
                AND bills.created_at <  '2017-03-01' + INTERVAL 1 MONTH
              GROUP BY DATE_FORMAT(bills.created_at,'%Y-%m-01')
           ) b

10-05 20:35
查看更多