我有一个存储user id
、article id
和article views
的文章表。我想从这个表中获取所有用户,并根据他们的文章总视图(sum) > 50
对他们进行排序。
见下表。
id | user_id | article_id | views
1 2 1 34
2 2 2 26
3 3 3 19
4 3 4 26
5 4 5 40
6 4 6 29
我想要这样的东西。
user_id | views
2 60
4 69
您可能希望使用加积函数sum
来汇总视图,然后使用group by
来给出所需的响应。
mysql> describe `blah`;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | | NULL | |
| article_id | int(11) | YES | | NULL | |
| views | int(11) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
mysql> select * from blah;
+----+---------+------------+-------+
| id | user_id | article_id | views |
+----+---------+------------+-------+
| 1 | 2 | 1 | 34 |
| 2 | 2 | 2 | 26 |
| 3 | 3 | 3 | 19 |
| 4 | 3 | 4 | 26 |
| 5 | 4 | 5 | 40 |
| 6 | 4 | 6 | 29 |
+----+---------+------------+-------+
/* use `SUM` and `GROUP BY` to give desired output */
mysql> select `user_id`, sum( `views` ) from `blah` group by `user_id`;
+---------+----------------+
| user_id | sum( `views` ) |
+---------+----------------+
| 2 | 60 |
| 3 | 45 |
| 4 | 69 |
+---------+----------------+
将记录限制在50 以上
mysql> select `user_id`, sum( `views` ) as 'total' from `blah` group by `user_id`
having `total` > 50;
+---------+-------+
| user_id | total |
+---------+-------+
| 2 | 60 |
| 4 | 69 |
+---------+-------+
您需要的是GROUP BY、SUM()和HAVING
您需要GROUP BY来告诉mysql您正在将同一用户的条目分组为一个条目。您需要SUM(视图)来告诉mysql,需要将同一用户的所有视图条目添加在一起。最后,您需要HAVING告诉mysql,需要满足按级别分组的条件才能被视为有效结果。
SELECT user_id, SUM(views) AS views
FROM table GROUP
GROUP BY user_id
HAVING SUM(views) > 50