来帮我看看这个mysql语句该怎么写!
程序员文章站
2022-06-07 16:29:34
...
test
name add num
a wefsv 3
b asdf 4
b wers 9
b w1rs 10
as asdf 11
as wers 12
as w3rs 13
例如上面这个表,我需要用select * from test......
来选出如下结果(每个name中num最大的那个,只要一行,其它不管!)
a wefsv 3
b w1rs 10
as w3rs 13
再多此一问,如果要选择2行怎么办?
b wers 9
b w1rs 10
as wers 12
as w3rs 13
a wefsv 3
大神!不行啊!每个选出num不是最大的
太牛了!昨天我弄了半小时都弄不出来!,谢谢!你这方法我感觉最好!
所以问题一的又一写法是
select * from test t
where 1>(select count(*) from test1 where num>t.num and name=t.name)
name add num
a wefsv 3
b asdf 4
b wers 9
b w1rs 10
as asdf 11
as wers 12
as w3rs 13
例如上面这个表,我需要用select * from test......
来选出如下结果(每个name中num最大的那个,只要一行,其它不管!)
a wefsv 3
b w1rs 10
as w3rs 13
再多此一问,如果要选择2行怎么办?
b wers 9
b w1rs 10
as wers 12
as w3rs 13
a wefsv 3
回复讨论(解决方案)
slect name,add,num from test group by add order by add desc
slect name,add,num from test group by add order by add desc
大神!不行啊!每个选出num不是最大的
每个name最大的一行
select *
from test a
where not exists (select 1 from test where name=a.name and num>a.num);
第一问
select * from (select * from test order by num desc) t group by name
name add num a wefsv 3 as w3rs 13 b w1rs 10
第一问
select * from (select * from test order by num desc) t group by name
name add num a wefsv 3 as w3rs 13 b w1rs 10
1 行
select * from test a where 0 = (select count(*) from test where name = a.name and num > a.num)
2 行
select * from test a where 2 > (select count(*) from test where name = a.name and num > a.num)
1 行
select * from test a where 0 = (select count(*) from test where name = a.name and num > a.num)
2 行
select * from test a where 2 > (select count(*) from test where name = a.name and num > a.num)
太牛了!昨天我弄了半小时都弄不出来!,谢谢!你这方法我感觉最好!
问题二
select * from test t where 2>(select count(*) from test where num>t.num and name=t.name);
name add num a wefsv 3 b wers 9 b w1rs 10 as wers 12 as w3rs 13
所以问题一的又一写法是
select * from test t
where 1>(select count(*) from test1 where num>t.num and name=t.name)