这样的一个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;
发表评论