mysql - 仅显示卡付款

问题描述

大约 6 年前，我设计了一个收银系统，虽然代码看起来很糟糕而且看起来很糟糕，但它仍在运行，我设计它的咖啡馆一直在使用它。

但是，他们最近购买了一台刷卡机，所以现在当他们想要查看当天的所有销售额时，他们希望在两个单独的表格中查看现金与卡。

事实证明这很棘手，并且它的 SQL 让我很困惑——我无法理解它。

以下是涉及的表格：

类别

+----+-------------------+---------+------------+
| id |       name        | display |     ts     |
+----+-------------------+---------+------------+
|  1 | Drinks            |       1 | 2016-10-14 |
|  2 | General Snacks    |       1 | 2016-10-14 |
|  3 | Lunch Options     |       1 | 2016-10-14 |
| 4  | Conference Drinks |       1 | 2016-10-14 |
+----+-------------------+---------+------------+

产品

+----+-----------------+-------+------+-----+---------+------------+
| id |      name       | price | cost | cID | display |     ts     |
+----+-----------------+-------+------+-----+---------+------------+
|  1 | English Tea     |   0.6 | 0.09 |   1 |       1 | 2018-02-15 |
|  2 | Speciality Teas |   0.8 | 0.17 |   1 |       1 | 2018-02-15 |
|  3 | Crisps          |   0.6 | 0.41 |   3 |       1 | 2018-02-15 |
|  4 | Chocolate Bar   |   0.6 |  0.5 |   3 |       1 | 2018-02-15 |
+----+-----------------+-------+------+-----+---------+------------+

收据

+----+-----+-----+----------+------------+
| id | oID | pID | quantity |     ts     |
+----+-----+-----+----------+------------+
|  1 |   1 |  26 |        1 | 2013-11-21 |
|  2 |   2 |   6 |        2 | 2013-11-21 |
|  3 |   3 |   2 |        1 | 2013-11-21 |
|  4 |   4 |   3 |        1 | 2013-11-21 |
+----+-----+-----+----------+------------+

销售量

+----+-------+----------+------+------+--------+------------+
| id | total | tendered | flag | card | userID |     ts     |
+----+-------+----------+------+------+--------+------------+
|  1 |     1 |        1 |    0 |    0 |      4 | 2013-11-21 |
|  2 |     2 |        2 |    0 |    0 |      4 | 2013-11-21 |
|  3 |   0.6 |      0.6 |    0 |    0 |      4 | 2013-11-21 |
|  4 |   0.6 |      0.6 |    0 |    0 |      4 | 2013-11-21 |
+----+-------+----------+------+------+--------+------------+

请记住，我很久以前写过这篇文章，我知道它并不完美。只是为了解释上面，oID代表orderID但真的应该是salesID和链接到销售表ID，并且pID代表productID和是链接到表的外键products。同样，cID是真的categoryID。

好的，所以咖啡馆经理要求一张如下所示的桌子：

+---------------+-----+------+-------+------------+-----------+
| Drinks        | Qty | Cost | Price | Cost-Total | Qty-total |
+---------------+-----+------+-------+------------+-----------+
| Juice Carton  | 2   | 33p  | 60p   | 66p        | £1.20     |
| Filter Coffee | 11  | 20p  | 80p   | £2.20      | £8.80     |
| Sub Total     | 13  |      |       | £2.86      | £10.00    |
| Grand Total   | 13  |      |       | £2.86      | £10.00    |
+---------------+-----+------+-------+------------+-----------+

这很好，我使用以下 SQL 语句来生成：

SELECT 
categories.name AS category, products.name, pID, 
(SELECT SUM(quantity) FROM receipts s WHERE s.pID = r.pID AND DATE(s.ts) = CURDATE()) AS quantity, 
products.price,
products.cost 
FROM receipts r 
LEFT JOIN products ON r.pID = products.id 
LEFT JOIN categories ON products.cID = categories.id 
WHERE DATE(r.ts) = CURDATE()
GROUP BY r.pID ORDER BY categories.name;

但是现在我需要显示两张表，一张用于卡，一张用于现金。sales现在卡付款在表中用1卡列中的a标记。所以我试着写这个：

SELECT 
categories.name AS category, products.name, pID, 
(SELECT SUM(quantity) FROM receipts s WHERE s.pID = r.pID AND DATE(s.ts) = CURDATE()) AS quantity, 
products.price,
products.cost 
FROM receipts r 

LEFT JOIN products ON r.pID = products.id 
LEFT JOIN sales x on r.oID = x.id 
LEFT JOIN categories ON products.cID = categories.id 

WHERE DATE(r.ts) = CURDATE() AND x.card = 1 
GROUP BY r.pID ORDER BY categories.name;

但是，它只是显示与第一个查询相同的数据。我知道问题出在哪里 - 它在嵌入式 SELECT 语句 ( AS quantity) 中，因为我没有在其中指定是卡付款还是现金付款。我认为只需添加x.card = 1一个结尾就可以了，但显然不是。

谁能帮我解决这个 SQL 问题？我如何将卡片条件放入嵌入式 SQL 中，因为它正在从收据中检索，并且收据不包含有关其是否为卡付款的信息？

我不知道如何真正进行。所有帮助将不胜感激！

标签： mysqlsql

mysql - 仅显示卡付款

问题描述

解决方案

推荐阅读