CREATE TABLE `t` (
`id` int(11) NOT NULL,
`cid` char(1) DEFAULT NULL,
`col1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
表中源数据:
mysql> select * from t order by cid ,col1 desc
;
+----+-----+------+
| id | cid | col1 |
+----+-----+------+
| 1 | A | 31 |
| 6 | A | 29 |
| 11 | A | 2 |
| 7 | B | 88 |
| 12 | B | 30 |
| 2 | B | 25 |
| 13 | C | 96 |
| 3 | C | 76 |
| 8 | C | 16 |
| 4 | D | 63 |
| 14 | D | 37 |
| 9 | D | 25 |
| 10 | E | 45 |
| 15 | E | 14 |
| 5 | E | 3 |
+----+-----+------+
15 rows in set
理想的查询结果: A B C D类别中第二大的行
id | cid | col1 |
6 A 29
2 B 25
3 C 76
14 D 37
10 E 45
------解决方案--------------------
分组+max应该能实现
select max(col1) col1, cid from t group by cid order by cid desc
//这是oracle写法,不知道mysql有没有max函数,试一下吧
------解决方案--------------------
不好意思,这几天有点忙,现在才看到!
问题解决了谢谢你!