查询“每月有多少用户提交内容”

Question

我想要得到过去4个月每个月有多少用户提交内容的统计数据。我正在考虑一个MySQL UNION，但我不认为它会工作，因为它可能会显示重复的行，因为AFAIK UNION只附加2个查询结果集。

我的表结构如下所示( Drupal中的一个典型的node表--用于Drupal专家)：

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid       | int(10) unsigned | NO   | UNI | 0       |                |
| type      | varchar(32)      | NO   | MUL |         |                |
| language  | varchar(12)      | NO   |     |         |                |
| title     | varchar(255)     | NO   | MUL |         |                |
| uid       | int(11)          | NO   | MUL | 0       |                |
| status    | int(11)          | NO   | MUL | 1       |                |
| created   | int(11)          | NO   | MUL | 0       |                |
| changed   | int(11)          | NO   | MUL | 0       |                |
| comment   | int(11)          | NO   |     | 0       |                |
| promote   | int(11)          | NO   | MUL | 0       |                |
| sticky    | int(11)          | NO   |     | 0       |                |
| tnid      | int(10) unsigned | NO   | MUL | 0       |                |
| translate | int(11)          | NO   | MUL | 0       |                |
+-----------+------------------+------+-----+---------+----------------+

保存节点提交时间的列是created。

Answer 1

也许是这样的(看起来不那么优雅……)

select count(distinct(t1.uid)) from node t1
inner join node t2 on t2.uid = t1.uid and t2.created between date_add(now(), interval -2 month) and date_add(now(), interval -1 month)
inner join node t3 on t3.uid = t1.uid and t3.created between date_add(now(), interval -3 month) and date_add(now(), interval -2 month)
inner join node t4 on t4.uid = t1.uid and t4.created between date_add(now(), interval -4 month) and date_add(now(), interval -3 month)
where t1.created between date_add(now(), interval -1 month) and now()

Answer 2

像这样的东西？

select extract(MONTH from create), count(distinct uid)
from node
where create >= DATE_ADD(month,-4,CURDATE())
group by extract(MONTH from create)

我还没有测试过..。

Answer 3

SELECT MONTH(create) as Month,
        (SELECT COUNT(vid) FROM Table WHERE MONTH(create) = Month)
FROM Table
ORDER BY Month DECS
LIMIT 4