获取每组中的最后一条记录并对其中一些进行求和
我对 mysql 的 sql 查询有问题,要获取每个组中的最后一条记录并对一个查询中的某些字段求和。我有一张表:
name date interested made_call
andrew.h 2011-02-04 10 10
andrew.h 2011-02-11 20 10
andrew.h 2011-02-13 2 10
sasha.g 2011-02-11 5 20
sasha.g 2011-02-12 5 1
我需要按名称对 made_call 列分组求和并返回感兴趣的最后一条记录。 这是我想要得到的结果:
name date interested made_call
andrew.h 2011-02-13 2 30
sasha.g 2011-02-12 5 21
我尝试通过此查询获取结果
SELECT a.name,a.date,a.interested,sum(made_call) as made_call
FROM `resultboard` a
WHERE a.attendence = 1
AND NOT EXISTS (select 1 from resultboard where name = a.name
and id > a.id and attendence = 1)
GROUP BY name
,但在结果中我得到的结果
andrew.h 2011-02-13 2 10
sasha.g 2011-02-12 5 1
是查询没有求和,只是返回组中的最后一条记录 帮助)
i have a problem with sql query to mysql to take the last record in each group and sum some field in one query.i have a table:
name date interested made_call
andrew.h 2011-02-04 10 10
andrew.h 2011-02-11 20 10
andrew.h 2011-02-13 2 10
sasha.g 2011-02-11 5 20
sasha.g 2011-02-12 5 1
i need to sum made_call column grouping by name and return the last record from interested.
here what i want to get in result:
name date interested made_call
andrew.h 2011-02-13 2 30
sasha.g 2011-02-12 5 21
i tried to get result with this query
SELECT a.name,a.date,a.interested,sum(made_call) as made_call
FROM `resultboard` a
WHERE a.attendence = 1
AND NOT EXISTS (select 1 from resultboard where name = a.name
and id > a.id and attendence = 1)
GROUP BY name
but in result i got
andrew.h 2011-02-13 2 10
sasha.g 2011-02-12 5 1
so the query didnot sum, just return the last record from group
help)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
如果表很大,这可能会有点慢,但它会得到想要的结果:
That may be a little slow if the table is very big, but it will get the wanted result:
您的 WHERE 子句将除最后一行之外的所有行作为总和的一部分排除在外。
在其他一些数据库中,您可以使用 LAST 聚合函数。 MySQL没有这个,但是你可以 对于您的情况,像这样模拟它:
它在大型数据集上可能不会很快,但如果我的研究是正确的,它至少应该可以完成工作。我还没有测试过这个,所以 YMMV。
Your WHERE clause is eliminating all but the last row from consideration as part of the sum.
In some other DB's you could use the LAST aggregate function. MySQL doesn't have that, but you can emulate it like so for your case:
It might not be fast on large data sets, but it should at least do the job if my research is correct. I haven't tested this, so YMMV.
我认为使用 WITH ROLLUP 作为 GROUP BY 修饰符可能会对您有所帮助。 http://dev.mysql.com/doc/ refman/5.0/en/group-by-modifiers.html
编辑;我错了,不需要WITH ROLLUP
I think that using WITH ROLLUP for GROUP BY modifier may help you. http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
Edit; I got i wrong , no need for WITH ROLLUP