I have following database (small example):
我有以下数据库(小例子):
ID | username | action | points | example
1 | matt | login | 3 | 135
2 | john | comment | 6 | 156
3 | john | login | 6 | 189
4 | peter | login | 8 | 125
5 | peter | login | 8 | 165
I wanted to select and group usernames which have the same action login and number of points higher than 5 (for all actions). All actions has to be login. So, results will be without john, because there is action comment related to this username.
我想选择和分组具有相同操作登录名和高于5的点数的用户名(对于所有操作)。所有操作都必须登录。因此,结果将没有john,因为有与此用户名相关的操作注释。
Related question: MySQL: Select only some grouped rows
相关问题:MySQL:仅选择一些分组行
Possible solution:
SELECT username, COUNT(*) AS cnt, SUM(points) AS points
FROM tableX AS t
GROUP BY username
HAVING MIN(action) = 'login'
AND MAX(action) = 'login'
AND SUM(points) > 5 ;
And results:
username | COUNT | points(SUM)
peter | 2 | 16
But then I would like to add to results all other values from row with highest ID. I tried subquery, but did not find the right solution.
但是,我想从具有最高ID的行添加结果中的所有其他值。我尝试了子查询,但找不到合适的解决方案。
Expected results:
ID | username | COUNT | points(SUM) | points | example
5 | peter | 2 | 16 | 8 | 165
Do you have any idea? Thank you very much!
你有什么主意吗?非常感谢你!
1 个解决方案
#1
0
Try this:
SELECT DISTINCT MAX(ID), username, action, SUM(points) as pointsSum, MAX(points) as pointsMax, MAX(example) as example
FROM tableX
WHERE action='login' GROUP BY username
#1
0
Try this:
SELECT DISTINCT MAX(ID), username, action, SUM(points) as pointsSum, MAX(points) as pointsMax, MAX(example) as example
FROM tableX
WHERE action='login' GROUP BY username