php - 从mysql中选择

Question

我有如下表格:

uid nid     points  date                        reason
36  116     2       2012-08-28 11:52:12         session
31  110     2       2012-08-23 15:47:47         session
36  115     2       2012-08-27 11:52:48         session

如您所见，uid 不是唯一的，它可以重复。我需要的是为日期(30 天前)之间的每个 ID 选择点总和。 例如:uid36 的总和 = 到 4。我尝试过的:

$start_time = mktime(0, 0, 0, $today["mon"], ($today["mday"] - 30), $today["year"]);//30 days before
$end_time = time();
$query = db_query("SELECT uid,sum(points), date FROM users_history WHERE date BETWEEN '$start_time' AND '$end_time'");

但是如何为每个id选择

Answer 1

您需要 GROUP BY uid，如果您使用聚合函数 SUM，则 date 在您的查询中毫无意义。

SELECT uid, SUM(points) 
FROM users_history 
WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 day) AND NOW() 
GROUP BY uid

如果没有 future 的日期，那么你只需要

WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 day)