表结构和数据createtablecat(id(1)intnotnullauto_incrementprimarykey,cat_idint(1),valueint(1),namevarchar(20));插入cat(cat_id,name,value)values('1','name1','1');插入cat(cat_id,name,value)values('1','name2','2');插入cat(cat_id,name,value)values('1','name3','3');插入cat(cat_id,name,value)values('1','name4','4');插入cat(cat_id,name,value)values('2','name5','5');插入cat(cat_id,name,value)values('2','name6','6');插入cat(cat_id,name,value)values('2','name7','7');插入cat(cat_id,name,value)values('2','name8','8');插入cat(cat_id,name,value)values('3','name9','9');插入cat(cat_id,name,value)values('3','name10','10');插入cat(cat_id,name,value)values('3','name11','11');插入cat(cat_id,name,value)values('3','name12','12');mysql>选择*fromcat;+----+--------+-------+------+|编号|猫ID|价值|名称|+----+--------+--------+--------+|1|1|1|名称1||2|1|2|姓名2||3|1|3|名字3||4|1|4|姓名4||5|2|5|名字5||62|6|名字6||7|2|7|姓名7||8|2|8|姓名8||9|3|9|名字9||10|3|10|名字10||11|3|11|姓名11||12|3|12|name12|+----+--------+--------+--------+12rowsinset(0.13sec)按组查询最大记录//默认取组中的第一项mysql>select*fromcatgroupbycat_idorderbycat_id;+----+--------+------+-------+|编号|猫ID|价值|名称|+----+--------+--------+--------+|1|1|1|名称1||5|2|5|名字5||9|3|9|name9|+----+--------+--------+-------+3rowsinset(0.00sec)mysql>select*from(select*fromcatorderbyvaluedesc)agroupbycat_id;+----+--------+-------+--------+|编号|猫_编号|价值|名称|+----+--------+--------+--------+|4|1|4|姓名4||8|2|8|姓名8||12|3|12|name12|+----+--------+--------+--------+3rowsinset(0.06sec)mysql>selecta.*fromcatawherevalue=(selectmax(value)fromcatwherecat_id=a.cat_id)orderbya.cat_id;mysql>selecta.*fromcata,(selectcat_id,max(value)valuefromcatgroupbycat_id)bwherea.cat_id=b.cat_idanda.value=b.valueorderbya.cat_id;mysql>selecta.*fromcatainnerjoin(selectcat_id,max(value)valuefromcatgroupbycat_id)bona.cat_id=b.cat_id和a.value=b.valueorderbya.cat_id;+----+--------+--------+--------+|编号|猫ID|价值|名称|+----+--------+--------+--------+|4|1|4|姓名4||8|2|8|姓名8||12|3|12|name12|+----+--------+--------+--------+3rowsinset(0.00sec)分组前3条记录mysql>selecta.*fromcatawhereexists(selectcount(*)来自catwherecat_id=a.cat_idandvalue>a.valuehavingCount(*)<3)orderbya.cat_id,a.valuedesc;mysql>select*fromcatawhere(selectcount(*)来自catbwherea.cat_id=b.cat_idandb.value>a.value)<3orderbya.cat_id,a.valuedesc;+----+--------+-------+--------+|编号|猫ID|价值|名称|+----+--------+--------+--------+|4|1|4|姓名4||3|1|3|名字3||2|1|2|姓名2||8|2|8|姓名8||7|2|7|姓名7||6|2|6|名字6||12|3|12|姓名12||11|3|11|姓名11||10|3|10|name10|+----+--------+--------+--------+9rowsinset(0.15sec)
