mysql - 如何在 SQL 中 SELECT MAX(column) as max_value FROM my_table WHERE some_other_column = "something"

Question

下面是我的 SQL 表。我正在尝试获取 time 的最大值，其中 to_address 是 dfhuih。

我试过:

SELECT MAX(time) as max_time FROM transactions WHERE to_address='dfhuih'

但是，当使用 PHP mysqli 时，该查询的结果给出了表中的最大 time 值，与 to_address 无关。

在指定了 to_address 的 time 列中选择最大值的正确方法是什么？

transactions
 ---------------------------------------
| to_address |     time     |  amount  |
----------------------------------------
|  dfhuih    | 1536294884   |  0.320   |
|  dfhuih    | 1536343222   |  0.564   |
|  adslsd    | 1546294884   |  0.320   |
|  vshjfof   | 1536294884   |  0.320   |
----------------------------------------

我的应用程序中的完整代码是

$stmt1 = $db_connection->prepare("SELECT MAX(time) as max_time FROM transactions WHERE to_address=? AND from_address=? AND is_affiliate_payment=0");
$stmt1->bind_param('is', $user_id, $faucet_key);
$stmt1->execute();
$result1 =$stmt1->get_result();

while($row = $result1->fetch_assoc())
{
   echo $row['max_time']    
}

Answer 1

问题出在这两行:

$stmt1 = $db_connection->prepare("SELECT MAX(time) as max_time 
                                  FROM transactions 
                                  WHERE to_address = ? AND 
                                        from_address = ? AND 
                                        is_affiliate_payment = 0");
$stmt1->bind_param('is', $user_id, $faucet_key);

您的查询中有两个 ?。第一个是 to_address，第二个是 from_address。两者都是字符串。然而，在您的 bind_param() 中，您要求一个整数 to_address。所以代码应该是:

$stmt1 = $db_connection->prepare("SELECT MAX(time) as max_time 
                                  FROM transactions 
                                  WHERE to_address = ? AND 
                                        from_address = ? AND 
                                        is_affiliate_payment = 0");
$stmt1->bind_param('ss', $to_address, $from_address);

请注意，我还更改了其余参数。 $user_id, $faucet_key 似乎不正确。检查一下!