我提出了以下方案:
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