如何在MySQL中显示付费和无薪订单

Question

客户表

+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+
| CustomerID | Fname    | Mname | Lname | Contact_no  | Address                     | Valid_id | Credit_Limit |
+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+
|          7 | John     | Dale  | Doe   | 09123654789 || NULL     |     5000.000 |
|          8 | Jane     | Dale  | Doe   | 09987654123 |  | NULL     |     1500.000 |
|         91 | Kurdapya | Buang | Selos | 09741258963 |  | NULL     |     5000.000 |
+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+

订单表

+---------+------------+----------------+----------+------------+
| OrderID | CustomerID | DateOfPurchase | Discount | DueDate    |
+---------+------------+----------------+----------+------------+
|      82 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      83 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      84 |          8 | 2022-04-17     | 0        | 2022-05-17 |
|      85 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      86 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      87 |         91 | 2022-04-18     | 0        | 2022-05-18 |
|     109 |          7 | 2022-04-25     | 0        | 2022-05-25 |
+---------+------------+----------------+----------+------------+

付款表

+-----------+------------+---------+------------+----------+
| PaymentID | CustomerID | OrderID | PayDate    | Amount   |
+-----------+------------+---------+------------+----------+
|        20 |          7 |      82 | 2022-04-25 |  800.000 |
|        21 |         91 |      83 | 2022-04-17 | 2500.000 |
|        22 |         91 |      85 | 2022-04-17 |  200.000 |
|        23 |         95 |      88 | 2022-04-18 | 2122.000 |
|        24 |         96 |      90 | 2022-04-25 |  577.000 |
|        25 |         97 |     111 | 2022-04-25 |    0.000 |
|        26 |         98 |     114 | 2022-04-25 |  166.000 |
|        27 |         99 |     115 | 2022-04-25 | 1740.000 |
+-----------+------------+---------+------------+----------+

订购详细信息表

+-------+---------+-----------+------+
| OR_ID | OrderID | ProductID | QTY  |
+-------+---------+-----------+------+
|   173 |      82 |         5 |    1 |
|   174 |      82 |         9 |    1 |
|   184 |      86 |         5 |    1 |
|   185 |      86 |         9 |    1 |
|   186 |      86 |        13 |    1 |
|   187 |      86 |        17 |    1 |
|   224 |     109 |         3 |    3 |
|   225 |     109 |         6 |    3 |
|   292 |     145 |        20 |    2 |
|   293 |     145 |        12 |    1 |
|   294 |     145 |         8 |    2 |
|   295 |     146 |        14 |    1 |
|   296 |     146 |        11 |    1 |
|   297 |     146 |        12 |    1 |
|   298 |     146 |         3 |    1 |
|   299 |     146 |         6 |    1 |
|   300 |     146 |         7 |    1 |
|   301 |     146 |        16 |    1 |
+-------+---------+-----------+------+

我不知道这是否需要产品表：

+-----------+---------------+-----------------------+------------+-----------+------+
| ProductID | Pname         | Pdesc                 | PUnitPrice | weight    | Unit |
+-----------+---------------+-----------------------+------------+-----------+------+
|         2 | Pepsi         | 1 Case Glass Bottle   |    313.000 | 1 Litre   |   12 |
|         3 | Mountain Dew  | 1 Case Glass Bottle   |    231.000 | 750 ML    |   12 |
|         4 | Pepsi         | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         5 | Mirinda       | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         6 | Mountain Dew  | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         7 | Mountain Dew  | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|         8 | Pepsi         | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|         9 | Mirinda       | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|        10 | 7up           | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|        11 | Sting         | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        12 | Tropicana     | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        13 | Cobra         | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        14 | Sting         | 1 Case Plastic Bottle |    166.000 | 300 ml    |   12 |
|        15 | Cobra         | 1 Case Plastic Bottle |    166.000 | 300 ml    |   12 |
|        16 | Mountain Dew  | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        17 | Mirinda       | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        18 | Pepsi         | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        19 | Ginebra       | 1 Case Glass Bottle   |    129.000 | 350 ml    |   24 |
|        20 | San Mig Light | 1 Case Glass Bottle   |   1070.000 | 330 ml    |   24 |
|        21 | Red Horse     | 1 Case Glass Bottle   |    535.000 | 500 ml    |   12 |
|        22 | Red Horse     | 1 Case Glass Bottle   |    545.000 | 1 Litre   |    6 |
+-----------+---------------+-----------------------+------------+-----------+------+

我想开始的

我想仅在一排中显示所有付费订单和他的未付订单。我不知道如何从查询开始。我应该使用if（）条件吗？如何正确查询它以实现我的欲望输出？

我想要的输出：

+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+
| unpaid | paid | Fname | Mname | Lname | Contact_no  | Address                     | Credit_Limit | total    |
+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+
| 2995   | 6014 | John  | Dale  | Doe   | 09123654789 |.                   |     5000.000 | 9009.000 |
+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+

编辑

这是我到目前为止尝试的

第1尝试： 我正在尝试计算未付订单，这是输出：

select 
    if(py.Amount IS NULL, sum(od.QTY * p.PUnitPrice), CONCAT(py.Amount - sum(od.QTY * p.PUnitPrice))) as remarks, 
    c.Fname, c.Mname, c.Lname, c.Contact_no, c.Address, c.Credit_Limit, 
    sum(od.QTY * p.PUnitPrice) as total 
from customer c INNER JOIN orders r on r.CustomerID=c.CustomerID
INNER join order_details od on r.OrderID=od.OrderID 
INNER JOIN product p on od.ProductID = p.ProductID 
join payment py 
where c.CustomerID=7 

尝试1：

+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+
| remarks     | Fname | Mname | Lname | Contact_no  | Address                     | Credit_Limit | total      |
+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+
| -134335.000 | John  | Dale  | Doe   | 09123654789 |                       |     5000.000 | 135135.000 |
+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+

第二尝试：

SELECT IF(py.OrderID IS NULL, sum(od.QTY * p.PUnitPrice), 0) AS unpaid,
if(py.OrderID IS NOT NULL, sum(od.QTY * p.PUnitPrice), 0) as paid,
sum(od.QTY * p.PUnitPrice) as total,
O.OrderID, O.CustomerID, date_format(O.DateOfPurchase, '%M %d, %Y') AS DateOfPurchase, date_format(O.DueDate, '%M %d, %Y') AS DueDate
from Orders O INNER JOIN order_details od on od.OrderID=O.OrderID
INNER JOIN product p ON od.ProductID=p.ProductID
LEFT JOIN Payment py ON py.OrderID = O.OrderID
where O.CustomerID = 7
GROUP by O.OrderID
ORDER by O.OrderID desc

尝试2的结果2 ：

+----------+----------+----------+---------+------------+----------------+--------------+
| unpaid   | paid     | total    | OrderID | CustomerID | DateOfPurchase | DueDate      |
+----------+----------+----------+---------+------------+----------------+--------------+
| 1829.000 |    0.000 | 1829.000 |     146 |          7 | April 27, 2022 | May 27, 2022 |
|    0.000 | 2696.000 | 2696.000 |     145 |          7 | April 27, 2022 | May 27, 2022 |
|    0.000 | 2553.000 | 2553.000 |     109 |          7 | April 25, 2022 | May 25, 2022 |
| 1166.000 |    0.000 | 1166.000 |      86 |          7 | April 17, 2022 | May 17, 2022 |
|    0.000 |  765.000 |  765.000 |      82 |          7 | April 17, 2022 | May 17, 2022 |
+----------+----------+----------+---------+------------+----------------+--------------+

注意：如何将列和无偿列中的所有行总结？

Answer 1

您接近解决方案的方式显然不起作用，因此您要寻求帮助。您的第一个查询，您正在加入付款表，但没有限制客户7的付款。这可能会偶然地奏效，因为只有一个付款记录。但是对于一个以上的客户来说
您的总数将通过笛卡尔产品结果偏向。

您应该做的是预先购买的购买与付款完全分开购买，因此您最多可以为您想要的一个客户提供单一记录。现在，如果您想将结果应用于所有客户，那将获得额外的额外费用，以后会覆盖。

每个人都可以防止多个混乱的混乱。

在最短的情况下，由于您只关心所购买的总计，因此我不在乎每个订单的总计，而只关心每个客户的总数。

select
        c.Fname, 
        c.Mname, 
        c.Lname, 
        c.Contact_no, 
        c.Address, 
        c.Credit_Limit, 
        coalesce( PQ_Orders.TotalOrders, 0 ) TotalOrders,
        coalesce( PQ_Payments.TotalPaid, 0 ) TotalPayments,
        coalesce( PQ_Orders.TotalOrders, 0 )
            - coalesce( PQ_Payments.TotalPaid, 0 ) BalanceDue
    from 
        customer c 
            LEFT JOIN
            ( select
                    o.customerID,
                    sum( od.qty * p.pUnitPrice ) TotalOrders
                from
                    orders o
                        join order_details od
                            on o.orderID = od.orderID
                            join product p
                                on od.productid = p.productid
                where
                    o.customerid = 7
                group by
                    o.customerID ) PQ_Orders
                on c.customerID = PQ_Orders.customerID
            LEFT JOIN
            ( select
                    py.customerid,
                    sum( py.Amount ) TotalPaid
                from
                    payments py
                where
                    py.customerid = 7
                group by
                    py.customerid ) PQ_Payments
                on c.customerID = PQ_Payments.CustomerID
    where 
        c.CustomerID = 7 

现在，如果您想要所有客户，只需删除所有条款，以便它可以吸引所有客户。每个内部查询都将客户ID作为数据分组，因此，如果一个人有10个订单和3个付款，则您仍然可以使用Per-Query（PQ）聚合，每个客户只有1个条目，每位客户1个条目付款。加入了主客户桌及其全面应用的所有板。

澄清O/P

以帮助澄清，让我们看看您的要求。对于单个客户，购买了多少东西。将其视为一个查询。根据订单详细信息和各自的产品价格，获取一个客户的所有订单，并将其汇总为客户分组。现在，客户的条款= 7仅仅是因为您与一个客户有关。但是，没有在哪里可以向所有客户展示他们各自的购买方式。您最多有（因为客户的群体ID），无论他们有1个订单，或274个订单。这是该客户的总数。

现在，付款的确切背景相同。我不在乎他们买东西什么或什么时候。我只关心给定客户的付款。类似地，无论有或没有客户ID = 7的位置，例如购买说明中。同样，每位客户的记录仅为1或75个付款，每个客户的记录1。

因此，现在，您有1：1：1的比率可能是1个客户与1个购买可能的比率。

左联接意味着我想要左边的东西（客户），但可以选择地在右侧找到一些东西（购买和/或付款）。

因此，cocce（）防止零在计算中绊倒事物。如果从给定的购买或付款的预购中有一个值，请获取，否则，假定为零。因此，您可以得到一个有问题的客户，或者通过删除条款的范围，您可以让所有客户的全部购买以及所有客户的全部付款，以获得所有客户的全部未付余额。