Mysql ,请大佬帮忙解决一下问题,救救孩子吧

Mysql ,请大佬帮忙解决一下问题,救救孩子吧
这样的一个table,怎么查出不同城市number最大的cl??

最终效果:

回答

select t1.* from (select city,max(number) mnum from city group by city ) t
left join (select * from city) t1 on t.city=t1.city; & W _ 6 and t.mnum = t1.nuw c xmh 7 3 : @ 4 I H {ber orden g U X & } ~ S 0r by t1.city;

表结构及数据如下:
CREATE TABLE city (
city varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
c1 varchar(255) CHARACA $ ? ? / l j `TER SET utf8d x h A y COLLATE utf8_general_ci NULL DEFAULT Nd o ) z AULL,
number int(11) NULL DEFAULT NULLU K ] l 3
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = D1 { i ^ O Aynamic;


-- Recop . 8rds ofr T | t w 7 4 3 ; city


INSERT INTO city VALUES ('北 o f (京', 'T', 20);
INSERT IN2 e RTO city VALU] ! [ ^ES ('北京', 'c', 50);
INSERT INTO city VALUES ('深圳', 'T', 50);
INSERT INTO city VALUES ('深圳', 'c', 20);
INSERT INTO city VALUES ('北京', 'T', 50);
INSERT INTO city VALUES ('上海', 'T', 20);

SET FOREIGN_KEY_CHEd a l ) { K 3 B ?CKS = 1;