id user number
1 test 34
2 qq 12
3 test 56
4 test 3
5 qq 1
6 bb 9现需要找出同一user里number最大的id,user,number是多少。。
也就是想得到这样的结果:id user number
3 test 56
2 qq 12
6 bb 9
我目前写法是
SELECT id,user,number
FROM table
WHERE number= max(number)
GROUP BY user但是出错。。
1 test 34
2 qq 12
3 test 56
4 test 3
5 qq 1
6 bb 9现需要找出同一user里number最大的id,user,number是多少。。
也就是想得到这样的结果:id user number
3 test 56
2 qq 12
6 bb 9
我目前写法是
SELECT id,user,number
FROM table
WHERE number= max(number)
GROUP BY user但是出错。。
FROM table
GROUP BY user
FROM table GROUP BY user;
id user number
1 test 34
2 qq 12
3 test 56
4 test 56
5 qq 1
6 bb 9
那么test 56取哪个id?(3?4?)
select *
from (select id,
user,
number,
row_number() over(partition by user order by number desc) as thenumber
from table) tmp
where tmp.thenumber = 1
应该有办法的吧
SELECT max(id) as id,user,max(number) as number
FROM table GROUP BY user;
SELECT id,user,max(number) as number
FROM table GROUP BY user order by number desc;
这样就可以了
(select user,max(number) as number from test_sql b group by user) b
where a.user=b.user and a.number=b.number
select * from user where number in (select max(number) number from user group by user);如果还要排序的话,就在后面加个order by吧。select * from user where number in (select max(number) number from user group by user) order by number DESC;
1 test 34
2 qq 12
3 test 56
4 test 3
5 qq 1
6 bb 9
7 test null
该如何处理?
前一段时间我也被这个问题困扰了N久,上网问了很多人和找了很多资料都没解决,于是狠下心来自己研究,总算解决了,用一个子查询就ok了。代码如下:
select * from user where number in (select max(number) number from user group by user);如果还要排序的话,就在后面加个order by吧。select * from user where number in (select max(number) number from user group by user) order by number DESC;
不行,不同user的number可能重复