mySql 对列求和并仅返回过去 10 分钟内包含 和 的条目
这是一个表,查询运行的时间即现在是 2010-07-30 22:41:14
number | person | timestamp
45 mike 2008-02-15 15:31:14
56 mike 2008-02-15 15:30:56
67 mike 2008-02-17 13:31:14
34 mike 2010-07-30 22:31:14
56 bob 2009-07-30 22:37:14
67 bob 2009-07-30 22:37:14
22 tom 2010-07-30 22:37:14
78 fred 2010-07-30 22:37:14
我想要一个可以将每个人的数字相加的查询。然后仅显示最近 60 分钟内有条目的姓名总数。困难似乎在于,尽管可以使用 AND timestamp
> now( ) - INTERVAL 600,这具有停止数字总和的效果。
我从上面得到的结果是
Mike 202
tom 22
fred 78
鲍勃不包括在内,他的最新条目不够新,已经一年了!迈克虽然有几个旧条目,但它是有效的,因为他最近有一个条目 - 但关键是,它仍然添加了他的完整“数量”,而不仅仅是那些具有时间段的条目。
继续通过一个查询来解决这个问题!谢谢安迪
。
heres a table, the time when the query runs i.e now is 2010-07-30 22:41:14
number | person | timestamp
45 mike 2008-02-15 15:31:14
56 mike 2008-02-15 15:30:56
67 mike 2008-02-17 13:31:14
34 mike 2010-07-30 22:31:14
56 bob 2009-07-30 22:37:14
67 bob 2009-07-30 22:37:14
22 tom 2010-07-30 22:37:14
78 fred 2010-07-30 22:37:14
Id like a query that can add up the number for each person. Then only display the name totals which have a recent entry say last 60 minutes. The difficult seems to be, that although its possible to use AND timestamp
> now( ) - INTERVAL 600, this has the affect of stopping the full sum of the number.
the results I would from above are
Mike 202
tom 22
fred 78
bob is not included his latest entry is not recent enough its a year old! mike although he has several old entries is valid because he has one entry recently - but key, it still adds up his full 'number' and not just those with the time period.
go on get your head round that one in a single query ! and thanks
andy.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要一个
HAVING
子句:You want a
HAVING
clause:安德鲁 - 本着教育的精神,我不会显示查询(实际上,我很懒,但不告诉任何人):)。
基本上,您必须在主要标准选择中进行子选择。在伪代码中,它会是:
那会爆炸,但你明白了要点......
吉姆
andrew - in the spirit of education, i'm not going to show the query (actually, i'm being lazy but don't tell anyone) :).
basically tho', you'd have to do a subselect within your main criteria select. in psuedo code it would be:
that will blow up, but you get the gist...
jim