：inwork
+----+------------+----------+------------+
| id sname smoney sprovice
+----+------------+----------+------------+
| 1 zhangsan 2098 A
+----+------------+----------+------------+
| 2 lishi 3000 B
+----+------------+----------+------------+
| 3 wangwu 6789 C
+----+------------+----------+------------+
| 4 liumazi 4587 C
+----+------------+----------+------------+
| 5 dongjiu 3298 B
+----+------------+----------+------------+
| 6 shiga 4567 A
+----+------------+----------+------------+
| 7 heiqi 4007 A
+----+------------+----------+------------+
| 8 diuba 3500 B
+----+------------+----------+------------+
| 9 behkanm 2900 C
+----+------------+----------+------------+
SQL:
+-------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `inwork` (
`id` int(11) NOT NULL auto_increment,
`sname` varchar(20) default NULL,
`smoney` int(11) default NULL,
`sprovice` varchar(12) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
INSERT INTO `inwork` VALUES (1, 'zhangsan', 2098, 'A');
INSERT INTO `inwork` VALUES (2, 'lishi', 3000, 'B');
INSERT INTO `inwork` VALUES (3, 'wangwu', 6789, 'C');
INSERT INTO `inwork` VALUES (4, 'liumazi', 4587, 'C');
INSERT INTO `inwork` VALUES (5, 'dongjiu', 3298, 'B');
INSERT INTO `inwork` VALUES (6, 'shiga', 4567, 'A');
INSERT INTO `inwork` VALUES (7, 'heiqi', 4007, 'A');
INSERT INTO `inwork` VALUES (8, 'diuba', 3500, 'B');
INSERT INTO `inwork` VALUES (9, 'behkanm', 2900, 'C');
+-------------------------------------------------------+
：
1. id ， ， ，
+-------------------------------------------+
|SELECT a.id, a.sname, a.smoney, a.sprovice |
|FROM inwork a, ( |
| SELECT max( smoney ) AS maxmoney, sprovice|
| FROM inwork |
| GROUP BY sprovice |
|)b |
|WHERE a.smoney = b.maxmoney |
|AND a.sprovice = b.sprovice |
+-------------------------------------------+
2. id ， ， ，
+-------------------------------------------+
|SELECT a.id, a.sname, a.smoney, a.sprovice |
|FROM inwork a, ( |
| SELECT avg( smoney ) AS avgmoney, sprovice|
| FROM inwork |
| GROUP BY sprovice |
|)b |
|WHERE a.smoney > b.avgmoney |
|AND a.sprovice = b.sprovice |
+-------------------------------------------+
3. ， （ ： ）
+-------------------------------------------+
|create table t1 as( |
| select avg(smoney) AS avgmoneys, sprovice |
| from inwork |
| group by sprovice) ; |
+-------------------------------------------+