如何将这种嵌套的php/mysql查询转换为更高效的查询？

Question

我正在寻找一些帮助，使我的php/MySQL代码更有效率。目前，我的PHP代码中有一条嵌套的SQL语句，它一直在运行。我知道有一种更有效的方法，在一条语句中执行查询，但我正在努力弄清楚如何做到这一点。

基本上，我有两张桌子。“顾客”和“购买”。我想浏览一下“客户”表，并计算/显示他们从“购买”表中购买了多少东西。

这是我的php/mysql代码：

$sql = "SELECT CustomerID, Username, Active FROM customers WHERE AND Active = 'Y'"; 
$result = mysql_query($sql,$connection);
while ($myrow = mysql_fetch_array($result)) {

    $sql2 = "SELECT COUNT(ID) FROM purchases WHERE AND (CustomerID = $myrow[CustomerID] AND (Date BETWEEN $date1 AND $date2) )
    $result2 = mysql_query($sql2,$connection);
    $TotalPurchases = mysql_result($result2,0,”count(ID)”);

}

以下哪项输出：

MrSmith: 10
MrsGreen: 4
MrGrey: 1
MissDonna: 0

如果不是用php计算的变量$date1和$date2 ( $date1和$date2之间的日期)，我可能会把它变成一个查询。有什么建议可以让这个查询更高效吗？

谢谢

卡洛斯

Answer 1

请尝试此查询，

SELECT c.CustomerID, Username, Active , COUNT(p.CustomerID)
    FROM customers as c
    left join purchases as p ON c.CustomerID = p.CustomerID
         AND Date BETWEEN $date1 AND $date2
    group by p.CustomerID 

我想这对你有帮助。

Answer 2

SELECT  c.CustomerID, c.Username, c.Active , COUNT(*)
    FROM  customers as c
    join  purchases as p ON c.CustomerID = p.CustomerID
      AND  p.Date BETWEEN $date1 AND $date2
    group by  p.CustomerID 

注意:加入，而不是左加入。计数(*)。

purchases应具有复合INDEX(Date, CustomerID)。

这可能会更快(编辑的)：

SELECT  c.CustomerID, c.Username, c.Active , p.ct
    FROM ( SELECT CustomerID, COUNT(*) ct FROM purchases
              WHERE Date BETWEEN $date1 AND $date2
              GROUP BY CustomerID ) p
    JOIN  customers AS c   ON c.CustomerID = p.CustomerID