php - 显示每个客户的未结订单数量

Question

我在一段代码上被困了一段时间，我找不到解决方案。尝试了一大堆选项，但似乎都不起作用。

我和所有顾客都坐在一张 table 上。它显示了他们的姓名、邮政编码等。但我还想在同一个表中显示未结订单的数量。

我得到了这些表 mysql 表:

表 1

表名:客户

列:customer_ID、邮政编码、customer_since、customer_name

表 2

表名:状态

列:status_ID、status_name

表 3

表名:订单

列:order_ID、customer_ID、status_ID

到目前为止，这是我的代码:

$sql = mysql_query ("SELECT customer.customer_ID, customer.postcode, customer.since, customer.name
FROM customer
ORDER BY customer.customer_ID desc  ");

echo '<table border="0" width="515"  >
      <tr>
    <td>
       <table cellspacing="0" cellpadding="0" border="0" width="515" id="table1" >
         <tr>
            <th width="60" align="center"><span class="tabledescription">Number:</span></td> //customernumber
        <th width="155" align="center"><span class="tabledescription">Name:</span></td> //customername
        <th width="100" align="center"><span class="tabledescription">Postcode:</span></td>//customerpostcode
        <th width="100" align="center"><span class="tabledescription">Orders open:</span></td>//amount of open orders
        <th width="100" align="center"><span class="tabledescription">Since:</span></td>//customer since
         </tr>
       </table>
    </td>
  </tr>
  <tr>
    <td>
       <div style="width:565px; height:322px; overflow:auto;">
         <table id="table1" cellspacing="0" cellpadding="0" border="0" width="575" >';

while($row = mysql_fetch_array($sql, MYSQL_ASSOC))
{
$id = $row['customer_ID'];
$name= $row['name'];
$postcode = $row['postcode'];
$status = $row['status'];
$since = $row['customer_since'];
$probleem = $row['probleem'];

$csince = date('d-m-Y', $since);

echo "<tr><td width=64><a style=' color: #009bce; text-decoration: none;' href='detailvieuwcustomer.php?id=".$id."'>".$id."</a></td>
        <td width=160>$name</td>
        <td width=105>$postcode</td>
        <td width=105>amount</td>
        <td width=105>$csince</td></tr>";

        }
echo ' </table>  
       </div>
    </td>
  </tr>
</table>'; 

到目前为止，该功能正在运行并显示我的 8 个客户。每个订单有 7 种不同的状态类型。最后一件事是它已经交付，所以没有开放。我编写了这段代码:

$statusnumber = 7;


$sql1 = mysql_query("SELECT * FROM order WHERE customer_ID = '". $id . " ' AND status_ID != '". $statusnumber . "' ");

while($prow = mysql_fetch_array($sql1, MYSQL_ASSOC))
{

$openstatus = $prow['storing_ID'];

echo $openstatus;

这个显示了每一个没有 status_ID 7 的订单。

现在我不知道如何计算 status_ID 1 - 6 的订单数量，并将未结订单数量放在正确客户后面的表中。

我也尝试加入表:

$sql = mysql_query("SELECT status.status_ID, order.status_ID, order.customer_ID, customer.customer_ID, customer.name, customer.postcode, customer.since
        FROM order
        INNER JOIN status on (status.status_ID = order.status_ID)
        INNER JOIN customer on (customer.customer_ID = order.customer_customer_ID)
        ORDER BY customer.customer_ID desc "); 

但是当我这样做时，它会多次向我显示所有客户，因为他从订单中获取 customer_ID，而我收到了大约 30 个订单。它给我的结果如下:1,1,1,1,2,2,2,3,4,4,5,5,5,5 等。

我似乎无法一次性显示所有客户以及他们已打开的订单数量。

如果有帮助，我们将不胜感激。

Answer 1

有几种方法。

其中之一是对 order 进行 OUTER JOIN table 。这里的技巧是对 customer_ID 进行 GROUP BY，并检查 status_ID列返回 0 或 1，然后使用 SUM 组聚合函数将 0 和 1 相加:

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , SUM(IF(s.status_ID != 7,1,0)) AS open_order_count
  FROM customer c
  LEFT
  JOIN order o
    ON o.customer_ID = c.customer_ID
  LEFT
  JOIN status s
    ON s.status_ID = o.status_ID
 GROUP
    BY c.customer_ID
     , c.postcode
     , c.since
     , c.name
 ORDER
    BY c.customer_ID DESC

注意:我们可以使用 COUNT聚合代替SUM ，但是我们需要为那些我们不想计数的行返回 NULL...

      , COUNT(IF(s.status_ID != 7,1,NULL)) AS open_order_count

<小时/>

另一种方法(通常在大型集合上性能较差)是在 SELECT 列表中使用相关子查询:

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , ( SELECT SUM(IF(s.status_ID != 7,1,0))
            FROM order o
            LEFT
            JOIN status s
              ON s.status_ID = o.status_ID
           WHERE o.customer_ID = c.customer_ID
        ) AS open_order_count
  FROM customer c
 ORDER BY c.customer_ID DESC

<小时/>

注意:为了性能，我可能会避免加入 status表，并快捷地检查 status_ID只需查看 order table 。 (这实际上取决于为什么 status 表被包含在查询中；我只是不认为这里需要它。)例如

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , ( SELECT SUM(IF(o.status_ID != 7,1,0))
            FROM order o
           WHERE o.customer_ID = c.customer_ID
        ) AS open_order_count
  FROM customer c
 ORDER BY c.customer_ID DESC

<小时/>

另一种方法是使用内联 View 来获取所有客户的未结订单计数，然后将其加入到客户表中...

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , IFNULL(r.open_order_count,0) AS open_order_count
  FROM customer c
  LEFT
  JOIN (
         SELECT o.customer_ID
              , SUM(IF(o.status_ID != 7,1,0)) AS open_order_count
           FROM order o
          GROUP
             BY o.customer_ID
       ) r
    ON r.customer_ID = o.customer_ID
 ORDER BY c.customer_ID DESC