php mysql 统计收藏,php – 什么mysql查询可以让我从这个表按月获得聚合统计数据？...

这在MySQL中并不容易.

首先,您需要创建一个变量表,一个用于存储当前组,另一个用于存储组中的当前行号.将它们初始化为NULL.

然后按月迭代并选择按分数排序的所有行,并选择当前的rown编号并增加它.如果组更改,请将行号重置为1.

然后将所有这些放在子选择中,在外部选择中,选择rownumber< = 3的所有行. 您可以使用此查询：

SELECT month, p_userid, points FROM (

SELECT

*,

(@rn := CASE WHEN month = @last_month THEN @rn + 1 ELSE 1 END) AS rn,

(@last_month := month)

FROM (

SELECT p_userid, month(p_timestamp) AS month, SUM(p_points) AS points

FROM Table1, (SELECT @last_month := NULL, @rn := 0) AS vars

GROUP BY p_userid, month(p_timestamp)

ORDER BY month, points DESC

) AS T1

) AS T2

WHERE rn <= 3

结果：

Month User Score

1 4 7

1 3 5

1 2 4

2 4 17

2 5 10

2 3 6

测试数据：

CREATE TABLE Table1 (p_userid INT NOT NULL,

p_points INT NOT NULL,

p_timestamp TIMESTAMP NOT NULL);

INSERT INTO Table1 (p_userid, p_points, p_timestamp) VALUES

(1, 1, '2010-01-01'),

(1, 2, '2010-01-02'),

(1, 3, '2010-02-01'),

(2, 4, '2010-01-01'),

(3, 5, '2010-01-01'),

(3, 6, '2010-02-01'),

(4, 7, '2010-01-01'),

(4, 8, '2010-02-01'),

(4, 9, '2010-02-02'),

(5, 10, '2010-02-02');