下面是我的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']
}
问题出在这两行:
$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
似乎不正确。检查一下!
您正在寻找,但如果没有示例数据和预期结果,很难说可以使用。
SELECT
transactions.*
FROM (
SELECT
MAX(time) AS max_time
FROM
transactions
WHERE
to_address = 'dfhuih'
) AS transactions_filter
INNER JOIN
transactions
ON
transactions_filter.max_time = transactions.time
AND
transactions.to_address = 'dfhuih'
请记住,如果两个 MAX 时间值相同,它也将显示领带
在MySQL 8中,您还可以将该查询重写到下面的查询中。
请注意,列别名作为列名移动到派生表别名。
SELECT
transactions.*
FROM (
SELECT
MAX(time)
FROM
transactions
WHERE
to_address = 'dfhuih'
) AS transactions_filter(max_time) # <- here is the column alias also defined.
INNER JOIN
transactions
ON
transactions_filter.max_time = transactions.time
AND
transactions.to_address = 'dfhuih'