表如下:
2 | lily | woman | 90 |
3 | lucy | woman | 85 |
4 | andy | man | 95 |
要查出man和woman的最高分,如下
sex | name | score |
man | andy | 95 |
woman | lily | 90 |
select 语句怎么写?
我是菜鸟,只会写这样 select sex,max(score) as score from score group by score; 能否不用子查询就把name也查出来?
1
Cee 2015-06-13 15:05:03 +08:00
SELECT name, sex, max(score) AS score FROM score GROUP BY sex;
|
3
caigaopei 2015-06-13 15:15:01 +08:00
select name, sex ,max(score) from score group by sex;
+------+-------+------------+ | name | sex | score | +------+-------+------------+ | tony | man | 95 | | lily | woman| 90 | +------+-------+------------+ |
5
Gonster 2015-06-13 15:19:05 +08:00
>.<要不按分数排个序
|
6
staticor 2015-06-13 15:19:08 +08:00
groupby max取得 socre + id+ sex, 再join 添加上name.
|
8
caigaopei 2015-06-13 15:43:05 +08:00
@xuyl select name,sex,max(score) from score group by name LIMIT 0,2; 这个笨办法不知道可行不..
+------+-------+------------+ | name | sex | max(score) | +------+-------+------------+ | andy | man | 95 | | lily | woman | 90 | +------+-------+------------+ |
10
feiyuanqiu 2015-06-13 15:57:51 +08:00
|
11
liboyue 2015-06-13 16:01:57 +08:00 via Android
这样行不?
select name, max(score) from score where sex = man |
12
zqhong 2015-06-13 16:52:12 +08:00
我的笨方法:
SELECT name, sex, max(score) AS score FROM score WHERE sex = man UNION SELECT name, sex, max(score) AS score FROM score WHERE sex = womon; |
13
bin456789 2015-06-13 17:13:50 +08:00
我也有个笨方法
在mssql上可用 SELECT * FROM ( SELECT TOP 1 Sex , Name , Score FROM dbo.Score WHERE Sex = 'man' ORDER BY Score DESC ) AS t1 UNION ALL SELECT * FROM ( SELECT TOP 1 Sex , Name , Score FROM dbo.Score WHERE Sex = 'woman' ORDER BY Score DESC ) AS t2; Sex Name Score -------------------------------------------------- -------------------------------------------------- ----------- man andy 95 woman lily 90 |
14
bin456789 2015-06-13 17:33:27 +08:00
找到一个更好的查询方法,请自己翻译到mysql中
SELECT * FROM ( SELECT * , RANK() OVER ( PARTITION BY Sex ORDER BY Score DESC ) AS rank FROM dbo.Score ) AS t WHERE t.rank = 1; |
15
kid813 2015-06-13 17:52:26 +08:00 via iPhone
在SELECT语句中,所有不出现在聚合函数(MAX)的列名都必须出现在GROUP BY中。
但是写GROUP BY name显然不是想要的结果,所以应该只能写子查询。 |
16
bigfella 2015-06-13 17:57:03 +08:00
子查询可行~ 笨方法
select name, sex, score from Score where score in (select max(score) from Score group by sex ) |
19
ETiV 2015-06-13 18:50:15 +08:00 via iPhone
这标题起的太误导人了……
MySQL 就没有能解决这类问题的“简单的SQL语句” |