Let me see how to write this mysql statement! 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
For example, in the preceding table, select * from test ......
To select the following result (the largest num in each name, as long as one line, no matter what else !)
A wefsv 3
B w1rs 10
As w3rs 13
What should I do if I want to select two rows?
B wers 9
B w1rs 10
As wers 12
As w3rs 13
A wefsv 3
Reply to discussion (solution)
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
Great God! No! Each selected num is not the largest
The maximum line of each name
Select *
From test
Where not exists (select 1 from test where name = a. name and num> a. num );
Question 1
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
Question 1
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 line
Select * from test a where 0 = (select count (*) from test where name = a. name and num> a. num)
2 rows
Select * from test a where 2> (select count (*) from test where name = a. name and num> a. num)
1 line
Select * from test a where 0 = (select count (*) from test where name = a. name and num> a. num)
2 rows
Select * from test a where 2> (select count (*) from test where name = a. name and num> a. num)
Awesome! I couldn't get it out for half an hour yesterday !, Thank you! I feel the best about you!
Question 2
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
So question 1 is written again
Select * from test t
Where 1> (select count (*) from test1 where num> t. num and name = t. name)