如何编写一个计算每月和每年行数的 SQL 查询? [英] How to write an SQL query that counts the number of rows per month and year?
问题描述
有没有人知道如何查询 vbulletin 数据库以生成关于每月/每年注册数量的报告,从而获得诸如......的结果.
Had anyone any idea how to query a vbulletin database to generate a report on the number of registrations per month/year to achive results like..
MM/YYYY Count
01/2001 : 10
02/2001 : 12
...
...
感谢下面的回答.我的最终版本如下:
Thanks to those answers below.. My final version that works is as follows:
SELECT
COUNT(*) as 'Registrations',
YEAR(FROM_UNIXTIME(joindate)) as 'Year',
MONTH(FROM_UNIXTIME(joindate)) as 'Month'
FROM vbfuser
GROUP BY Year,Month
推荐答案
我不熟悉 vBulletin 的数据库结构,但你应该做这样的事情,假设你的用户表有一个日期/日期时间/timestamp created_date
或 reg_timestamp
列或类似的东西,使用 MySQL 的 YEAR() 和 MONTH() 函数.
I am not familiar with vBulletin's database structure, but you should do something like this, assuming your user table has a date/datetime/timestamp created_date
or reg_timestamp
column or something similiar, using MySQL's YEAR() and MONTH() functions.
select
count(*) as count,
year(reg_timestamp) as year
month(reg_timestamp) as month
from users
group by year, month;
这将导致类似的结果:
+-------+-------+------+
| count | month | year |
+-------+-------+------+
| 4 | 11 | 2008 |
| 1 | 12 | 2008 |
| 196 | 12 | 2009 |
| 651 | 1 | 2010 |
+-------+-------+------+
关于 Dave 的评论: vBulletin 的日期似乎以 Unixtime 格式存储.在这种情况下,只需用 FROM_UNIXTIME
包装该列即可将其转换为可读的 MySQL 日期:
regarding Dave's comment: vBulletin's date seems to be stored in Unixtime format. In this case, simply wrapping the column with FROM_UNIXTIME
will convert it to a readable MySQL date:
select
count(*) as count,
year(from_unixtime(reg_timestamp)) as year
month(from_unixtime(reg_timestamp)) as month
from users
group by year, month;
这篇关于如何编写一个计算每月和每年行数的 SQL 查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!