是否有办法在php中获得大多数购买的包？

Question

我在php中有一个web应用程序。在那里，用户可以购买软件包，管理员可以看到每个软件包有多少销售。现在，我正在通过计算付款表中的行来获得总销售额。为了获得最多的购买包，我想我必须在数据库中的payments表中数行，并返回大多数购买包的id。但我不想这么做？

从Id获取包销售的当前语句：

$sql = "SELECT COUNT(*) AS totalSales FROM ph_paypal_payments WHERE packageId = ?";
$stmt = mysqli_stmt_init($connection);
if (!mysqli_stmt_prepare($stmt, $sql)) die ("Prepare failed: " . mysqli_stmt_error($stmt));

mysqli_stmt_bind_param($stmt, "i", $packageId);
mysqli_stmt_execute($stmt);
return mysqli_fetch_assoc(mysqli_stmt_get_result($stmt))['totalSales'];

Answer 1

你可以用这个

SELECT 
  packageId
FROM
  (SELECT packageId, COUNT(*) AS totalSales FROM ph_paypal_payments 
  GROUP BY packageId) t1
ORDER BY totalSales DESC
LIMIT 1;

Ir为您提供one packageId，它拥有最高的totalSales。即使还有更多，你也只能得到其中之一。

这是基于行数而不是销售量。

$sql = "SELECT packageId FROM (SELECT packageId, COUNT(*) AS totalSales FROM ph_paypal_payments GROUP BY packageId) t1 ORDER BY totalSales DESC LIMIT 1;";
$stmt = mysqli_stmt_init($connection);
if (!mysqli_stmt_prepare($stmt, $sql)) die ("Prepare failed: " . mysqli_stmt_error($stmt));

mysqli_stmt_bind_param($stmt, "i", $packageId);
mysqli_stmt_execute($stmt);
return mysqli_fetch_assoc(mysqli_stmt_get_result($stmt))['packageId'];