我需要获得每个库存商的最新交易金额，而不是他们的交易总额。
有两张桌子库存商
库存表
stid memberid stockistname area_c
1 4 Roger Sp Park
2 6 John Little Garden
3 77 Lily White D
4 32 Meredith Dare street
发生业务表
trans_id memberid trans_type amount trans_date
300 4 cp 250 2015-12-01 00:00:56
301 6 cp 100 2015-12-01 01:20:56
302 6 cp 130 2015-12-03 11:03:51
303 77 cp 74 2015-12-03 13:03:51
304 32 cp 25 2015-12-04 11:00:02
305 6 cp 425 2015-12-04 17:00:02
306 4 cp 235 2015-12-05 06:00:02
我的查询：
SELECT * FROM stockist LEFT JOIN (
SELECT * FROM transactions WHERE trans_type='cp' ORDER BY trans_id DESC LIMIT 1 )
transactions ON stockist.memberid=transactions.memberid ORDER BY stockist.stockistname ASC
结果是：
John $0
Lily $0
Meredith $0
Roger $0
我得到了所有库存商的名字，但金额为零，它假设显示
John $425
Lily $74
Meredith $25
Roger $235
实际上，在我的PHP代码中，我的代码看起来像这样，但它不够快。我希望让它更快。
$x=1;
$sql = mysql_query("SELECT * FROM stockist ORDER BY stockistname ASC");
while($row=mysql_fetch_array($sql)){
$memberid=$row['memberid'];
$stockistname=$row['stockistname'];
$sql2 = mysql_query("SELECT * FROM transactions WHERE memberid='$memberid' AND trans_type='cp' ORDER BY trans_id LIMIT 1");
$row2=mysql_fetch_array($sql2);
$latest_amount=$row2['amount'];
echo "<p> $x - $stockistname $ $latest_amount </p>";
$x++;
}
溶液：
select * from
stockist, (select * from transactions where trans_type='cp' ORDER BY trans_id DESC) transactions
where
stockist.memberid=transactions.memberid
GROUP BY memberid
ORDER BY stockist.stockistname ASC
谢谢大家
您的限制导致问题。尝试使用相关查询来比较最大日期，如下所示：
SELECT * FROM stockist s
LEFT OUTER JOIN transactions t
ON(t.memberid = s.memberid and t.trans_type = 'cp')
WHERE t.trans_date = (select max(f.trans_date) from transactions f
where f.memberid = t.memberid and f.trans_type = 'cp')
尝试按
金额订购
SELECT * FROM stockist
LEFT JOIN transactions ON stockist.memberid = transactions.memberid
WHERE trans_type='cp'
GROUP BY stockistname ORDER BY amount DESC LIMIT 1
试试这个：
select stockist.stockistname, amount from
stockist, (select memberid,amount,MAX(trans_date) from transactions where trans_type='cp' GROUP BY memberid) transactions
where
stockist.memberid=transactions.memberid
ORDER BY stockist.stockistname ASC