我有跑步的SQL表信息,如时间,距离,配速...我想获得用户每月最佳最大跑步距离的信息。我需要每个月计算跑步距离并找到最好的一个(MAX(。
我的表如下所示:
+----+--------+--------+----------+
| ID | USERID | MONTH | DISTANCE |
+----+--------+--------+----------+
| 1 | 1 | 201707 | 6.69 |
+----+--------+--------+----------+
| 2 | 2 | 201707 | 6.03 |
+----+--------+--------+----------+
| 3 | 2 | 201707 | 6.02 |
+----+--------+--------+----------+
| 4 | 1 | 201706 | 7.25 |
+----+--------+--------+----------+
| 5 | 1 | 201706 | 7.13 |
+----+--------+--------+----------+
| 6 | 2 | 201705 | 7.03 |
+----+--------+--------+----------+
| 7 | 3 | 201705 | 6.31 |
+----+--------+--------+----------+
| 8 | 1 | 201705 | 5.28 |
+----+--------+--------+----------+
| 9 | 1 | 201704 | 4.44 |
+----+--------+--------+----------+
| 10 | 2 | 201704 | 5.94 |
+----+--------+--------+----------+
我尝试这个,但我没有输出:
$select3run = mysqli_query($conn, "SELECT month, MAX(mycount) FROM (SELECT month, SUM(distance) AS mycount FROM runbeh GROUP BY month, distance) a GROUP BY month");
while($data=mysqli_fetch_array($select3run)) {
$max=$data['mycount'];
}
echo $max;
不知道如何找到我正在寻找的价值。有人可以帮助我吗?
您应该为列名使用适当的别名,例如:my_max
$select3run = mysqli_query($conn, "
SELECT month, MAX(mycount) as my_max
FROM (SELECT month, SUM(distance) AS mycount
FROM runbeh GROUP BY month, distance) a
GROUP BY month");
while($data=mysqli_fetch_array($select3run)) {
$max=$data['my_max'];
}
echo $max;
示例:
http://sqlfiddle.com/#!9/29009f/8
.SQL:
SELECT r.userid, r.month, max(r.sumdistance)
FROM
(
SELECT userid, month, sum(distance) as sumdistance
FROM runners
GROUP BY month, userid
) as r
GROUP BY r.userid
解决了!
非常感谢Raimondas Kazlauskas和scaisEdge,他们为我指明了道路并帮助了我。
显示用户每月运行的最大距离的工作代码:
$select3run = mysqli_query($conn, "SELECT month, MAX(mycount) as my_max
FROM
(SELECT month, SUM(distance) AS mycount FROM runners GROUP BY month) a GROUP
BY mycount DESC LIMIT 1");
while($data=mysqli_fetch_array($select3run)) {
$max=$data['my_max'];
echo $max; }
其中$max是用户一个月运行的最大距离总和。
你应该试试这个:-
每个用户每月最好运行:-
选择最大(距离(作为最大距离,用户 ID 来自 runbeh 组按月,用户 ID
注意 :- 永远不要在 Group BY 子句中放置任何聚合函数