为啥我的结果和 lz 的不一样呢? [code] mysql> select * from student; +------+-------+--------+--------+-------+ | name | yuwen | shuxue | yingyu | banji | +------+-------+--------+--------+-------+ | 小明 | 80 | 80 | 100 | 一班 | | 小明 | 100 | 80 | 80 | 一班 | | 小张 | 80 | 100 | 80 | 一班 | | 小张 | 80 | 80 | 80 | 一班 | +------+-------+--------+--------+-------+ 4 rows in set (0.00 sec)mysql> select -> max(if(yuwen = max_yuwen, name, "")) as yuwen_name, -> max_yuwen as yuwen, -> max(if(shuxue = max_shuxue, name, "")) as shuxue_name, -> max_shuxue as shuxue, -> max(if(yingyu = max_yingyu, name, "")) as yingyu_name, -> max_yingyu as yingyu -> from -> student left join -> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy u) as max_yingyu from student) as max_score -> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu -> group by banji; +------------+-------+-------------+--------+-------------+--------+ | yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu | +------------+-------+-------------+--------+-------------+--------+ | 小明 | 100 | 小张 | 100 | 小明 | 100 | +------------+-------+-------------+--------+-------------+--------+ 1 row in set (0.00 sec)mysql>[/code]为了做区别,把分数改成不一样: mysql> select * from student; +------+-------+--------+--------+-------+ | name | yuwen | shuxue | yingyu | banji | +------+-------+--------+--------+-------+ | 小明 | 80 | 80 | 99 | 一班 | | 小明 | 100 | 80 | 80 | 一班 | | 小张 | 80 | 98 | 80 | 一班 | | 小张 | 80 | 80 | 80 | 一班 | +------+-------+--------+--------+-------+ 4 rows in set (0.00 sec)mysql> select -> max(if(yuwen = max_yuwen, name, "")) as yuwen_name, -> max_yuwen as yuwen, -> max(if(shuxue = max_shuxue, name, "")) as shuxue_name, -> max_shuxue as shuxue, -> max(if(yingyu = max_yingyu, name, "")) as yingyu_name, -> max_yingyu as yingyu -> from -> student left join -> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy u) as max_yingyu from student) as max_score -> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu -> group by banji; +------------+-------+-------------+--------+-------------+--------+ | yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu | +------------+-------+-------------+--------+-------------+--------+ | 小明 | 100 | 小张 | 98 | 小明 | 99 | +------------+-------+-------------+--------+-------------+--------+ 1 row in set (0.00 sec)
为啥我的结果和 lz 的不一样: mysql> select * from student; +------+-------+--------+--------+-------+ | name | yuwen | shuxue | yingyu | banji | +------+-------+--------+--------+-------+ | 小明 | 80 | 80 | 100 | 一班 | | 小明 | 100 | 80 | 80 | 一班 | | 小张 | 80 | 100 | 80 | 一班 | | 小张 | 80 | 80 | 80 | 一班 | +------+-------+--------+--------+-------+ 4 rows in set (0.00 sec)mysql> select -> max(if(yuwen = max_yuwen, name, "")) as yuwen_name, -> max_yuwen as yuwen, -> max(if(shuxue = max_shuxue, name, "")) as shuxue_name, -> max_shuxue as shuxue, -> max(if(yingyu = max_yingyu, name, "")) as yingyu_name, -> max_yingyu as yingyu -> from -> student left join -> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy u) as max_yingyu from student) as max_score -> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu -> group by banji; +------------+-------+-------------+--------+-------------+--------+ | yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu | +------------+-------+-------------+--------+-------------+--------+ | 小明 | 100 | 小张 | 100 | 小明 | 100 | +------------+-------+-------------+--------+-------------+--------+ 1 row in set (0.00 sec)mysql>
忘记加上班级了,这英语和拼音一起的混合,真是很别扭啊: mysql> select -> banji, -> max(if(yuwen = max_yuwen, name, "")) as yuwen_name, -> max_yuwen as yuwen, -> max(if(shuxue = max_shuxue, name, "")) as shuxue_name, -> max_shuxue as shuxue, -> max(if(yingyu = max_yingyu, name, "")) as yingyu_name, -> max_yingyu as yingyu -> from -> student left join -> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy u) as max_yingyu from student) as max_score -> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu -> group by banji; +-------+------------+-------+-------------+--------+-------------+--------+ | banji | yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu | +-------+------------+-------+-------------+--------+-------------+--------+ | 一班 | 小明 | 100 | 小张 | 100 | 小明 | 100 | +-------+------------+-------+-------------+--------+-------------+--------+ 1 row in set (0.00 sec)
select * from (select name,yuwen from student order by yuwen desc limit 1) a, (select name,shuxue from student order by shuxue desc limit 1) b, (select name,yingyu from student order by yingyu desc limit 1) c
select * from (select name,yuwen from student where banji='一班' order by yuwen desc limit 1) y, (select name,shuxue from student where banji='一班' order by shuxue desc limit 1) s, (select name,yingyu from student where banji='一班' order by yingyu desc limit 1) eng
[code]
mysql> select * from student;
+------+-------+--------+--------+-------+
| name | yuwen | shuxue | yingyu | banji |
+------+-------+--------+--------+-------+
| 小明 | 80 | 80 | 100 | 一班 |
| 小明 | 100 | 80 | 80 | 一班 |
| 小张 | 80 | 100 | 80 | 一班 |
| 小张 | 80 | 80 | 80 | 一班 |
+------+-------+--------+--------+-------+
4 rows in set (0.00 sec)mysql> select
-> max(if(yuwen = max_yuwen, name, "")) as yuwen_name,
-> max_yuwen as yuwen,
-> max(if(shuxue = max_shuxue, name, "")) as shuxue_name,
-> max_shuxue as shuxue,
-> max(if(yingyu = max_yingyu, name, "")) as yingyu_name,
-> max_yingyu as yingyu
-> from
-> student left join
-> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy
u) as max_yingyu from student) as max_score
-> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu
-> group by banji;
+------------+-------+-------------+--------+-------------+--------+
| yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu |
+------------+-------+-------------+--------+-------------+--------+
| 小明 | 100 | 小张 | 100 | 小明 | 100 |
+------------+-------+-------------+--------+-------------+--------+
1 row in set (0.00 sec)mysql>[/code]为了做区别,把分数改成不一样:
mysql> select * from student;
+------+-------+--------+--------+-------+
| name | yuwen | shuxue | yingyu | banji |
+------+-------+--------+--------+-------+
| 小明 | 80 | 80 | 99 | 一班 |
| 小明 | 100 | 80 | 80 | 一班 |
| 小张 | 80 | 98 | 80 | 一班 |
| 小张 | 80 | 80 | 80 | 一班 |
+------+-------+--------+--------+-------+
4 rows in set (0.00 sec)mysql> select
-> max(if(yuwen = max_yuwen, name, "")) as yuwen_name,
-> max_yuwen as yuwen,
-> max(if(shuxue = max_shuxue, name, "")) as shuxue_name,
-> max_shuxue as shuxue,
-> max(if(yingyu = max_yingyu, name, "")) as yingyu_name,
-> max_yingyu as yingyu
-> from
-> student left join
-> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy
u) as max_yingyu from student) as max_score
-> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu
-> group by banji;
+------------+-------+-------------+--------+-------------+--------+
| yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu |
+------------+-------+-------------+--------+-------------+--------+
| 小明 | 100 | 小张 | 98 | 小明 | 99 |
+------------+-------+-------------+--------+-------------+--------+
1 row in set (0.00 sec)
mysql> select * from student;
+------+-------+--------+--------+-------+
| name | yuwen | shuxue | yingyu | banji |
+------+-------+--------+--------+-------+
| 小明 | 80 | 80 | 100 | 一班 |
| 小明 | 100 | 80 | 80 | 一班 |
| 小张 | 80 | 100 | 80 | 一班 |
| 小张 | 80 | 80 | 80 | 一班 |
+------+-------+--------+--------+-------+
4 rows in set (0.00 sec)mysql> select
-> max(if(yuwen = max_yuwen, name, "")) as yuwen_name,
-> max_yuwen as yuwen,
-> max(if(shuxue = max_shuxue, name, "")) as shuxue_name,
-> max_shuxue as shuxue,
-> max(if(yingyu = max_yingyu, name, "")) as yingyu_name,
-> max_yingyu as yingyu
-> from
-> student left join
-> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy
u) as max_yingyu from student) as max_score
-> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu
-> group by banji;
+------------+-------+-------------+--------+-------------+--------+
| yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu |
+------------+-------+-------------+--------+-------------+--------+
| 小明 | 100 | 小张 | 100 | 小明 | 100 |
+------------+-------+-------------+--------+-------------+--------+
1 row in set (0.00 sec)mysql>
mysql> select
-> banji,
-> max(if(yuwen = max_yuwen, name, "")) as yuwen_name,
-> max_yuwen as yuwen,
-> max(if(shuxue = max_shuxue, name, "")) as shuxue_name,
-> max_shuxue as shuxue,
-> max(if(yingyu = max_yingyu, name, "")) as yingyu_name,
-> max_yingyu as yingyu
-> from
-> student left join
-> (select max(yuwen) as max_yuwen, max(shuxue) as max_shuxue, max(yingy
u) as max_yingyu from student) as max_score
-> on yuwen = max_yuwen or shuxue = max_shuxue or yingyu = max_yingyu
-> group by banji;
+-------+------------+-------+-------------+--------+-------------+--------+
| banji | yuwen_name | yuwen | shuxue_name | shuxue | yingyu_name | yingyu |
+-------+------------+-------+-------------+--------+-------------+--------+
| 一班 | 小明 | 100 | 小张 | 100 | 小明 | 100 |
+-------+------------+-------+-------------+--------+-------------+--------+
1 row in set (0.00 sec)
from
(select name,yuwen from student order by yuwen desc limit 1) a,
(select name,shuxue from student order by shuxue desc limit 1) b,
(select name,yingyu from student order by yingyu desc limit 1) c
from
(select name,yuwen from student where banji='一班' order by yuwen desc limit 1) y,
(select name,shuxue from student where banji='一班' order by shuxue desc limit 1) s,
(select name,yingyu from student where banji='一班' order by yingyu desc limit 1) eng