MySQL - 项目总和问题

Question

我正在使用 MySQL 和 PHP 开发一个基于网络的报告系统作为学校项目。这就是我的数据库的样子。

+-----------------------------------------------+
|               Bill                            |
|-----------------------------------------------|
|   BillID (pk) |   BillDate    |   AccID (fk)  |
|---------------|---------------|---------------|
|   1           |  xxxx-xx-xx   |   1           |   
|   2           |  xxxx-xx-xx   |   2           |       
+---------------+---------------+---------------+


+---------------------------------------------------------------+
|                 BillItem                                      |
|---------------------------------------------------------------|
|BillItemID (pk)|   Quantity    |   BillID (fk) |ProductID (fk) |
|---------------|---------------|---------------|---------------|
|   1           |         10    |       1       |       1       |   
|   2           |         10    |       1       |       2       |       
|   3           |         10            2       |       2       |
+---------------+---------------+---------------+---------------+

+-----------------------------------------------+
|                    Product                    |
|-----------------------------------------------|
| ProductID(pk) |   ProductName |     Price     |
|---------------|---------------|---------------|
|   1           |   Product1    |   XX          |
|   2           |   Product2    |   XX          |   
|   3           |   Product3    |   XX          |
+---------------+---------------+---------------+


+-------------------------------+
|            Account            |
|-------------------------------|
|   AccID(pk)   |   UserName    |
|---------------|---------------|
|   1           |   AA          |   
|   2           |   BB          |       
+---------------+---------------+

用户购买产品。系统为每个产品生成一个账单项目。一张账单可以有多个账单项目。用户可以稍后再次购买相同的元素、不同的元素或两者的组合。我想了解用户在特定时间段内从每种产品购买了多少件商品。

预期:

+-----------------------------------------------+
|   ProductName |   UserName    |    Quantity   |
|---------------|---------------|---------------|
|    Product1   |      AA       |      10       |   
|    Product2   |      AA       |      10       |       
|    Product3   |      AA       |       0       |
|    Product1   |      BB       |       0       |   
|    Product2   |      BB       |      10       |       
|    Product3   |      BB       |       0       |
+---------------+---------------+---------------+

到目前为止我的尝试:

SELECT Product.ProductName AS ProductName, 
Account.UserName AS UserName,
SUM( Billitem.Quantity ) AS Quantity
FROM billitem
INNER JOIN Product ON BillItem.ProductID = Product.ProductID
INNER JOIN Bill ON Billitem.BillID = Bill.BillID
INNER JOIN Account ON Bill.AccID = Account.AccID
WHERE Bill.BillDate >=  'xxxx-xx-xx'AND Bill.BillDate <=  'xxxx-xx-xx'
GROUP BY UserName, ProductName

结果:

|-----------------------------------------------|
|   ProductName |   UserName    |    Quantity   |
|---------------|---------------|---------------|
|    Product1   |      AA       |   10          |   
|    Product2   |      AA       |   10          |       
|    Product2   |      BB       |   10          |
+---------------+---------------+---------------+

有什么办法可以用 mysql 查询实现吗？

Answer 1

您有几个问题正在处理以获得您正在寻找的结果。首先，您在 Product 和 Account 表之间没有直接关系，但您希望显示两者的笛卡尔积。您可以使用 CROSS JOIN 来产生那些想要的结果。

其次，您需要对 Bill 和 BillItem 表使用 OUTER JOIN 并移动您的 WHERE JOIN 的标准。

SELECT 
    ProductAccounts.ProductName AS ProductName, 
    ProductAccounts.UserName AS UserName,
    COALESCE(SUM(Billitem.Quantity),0) AS Quantity
FROM (
         SELECT P.ProductId, P.ProductName, A.AccId, A.UserName
         FROM Product P CROSS JOIN Account A
    ) ProductAccounts
    LEFT JOIN billitem ON BillItem.ProductID = ProductAccounts.ProductID
    LEFT JOIN Bill ON Billitem.BillID = Bill.BillID 
         AND Bill.BillDate >=  'xxxx-xx-xx'
         AND Bill.BillDate <=  'xxxx-xx-xx'
GROUP BY UserName, ProductName

这是一篇有助于理解联接的有用文章:

• A Visual Explanation of SQL Joins