一: Select a.name,a.kemu,a.fenshu,(Select avg(b.fenshu) From t_grade b where a.kemu=b.kemu GROUP BY b.kemu ) From t_grade a Where a.fenshu<(Select avg(b.fenshu) From t_grade b where a.kemu=b.kemu GROUP BY b.kemu ) GROUP BY a.kemu二: SELECT name,kemu,fenshu from t_grade where name not in(select name from t_grade where fenshu<=80)三: SELECT name as 姓名, MAX( CASE WHEN kemu='语文' THEN fenshu END ) AS 语文, MAX( CASE WHEN kemu='数学' THEN fenshu END ) AS 数学
FROM t_grade GROUP BY name 四: select name,kemu, case when fenshu>=90 then '优秀' when fenshu<90 and fenshu>=80 then '中等' when fenshu<80 then '合格' end as fenshu from t_grade mysql5.6 上验证。
select a.*,b.avg_fenshu FROM chengji a LEFT JOIN (select kemu,AVG(fenshu) as avg_fenshu FROM chengji GROUP BY kemu) b on a.kemu = b.kemu where a.fenshu < b.avg_fenshuselect name,GROUP_CONCAT(fenshu) from chengji where fenshu > 80 GROUP BY NAME HAVING count(*) > 1SELECT a. NAME, substring_index(a.fenshus, ',', 1) AS '数学', substring_index(a.fenshus, ',', - 1) AS '语文' FROM ( SELECT NAME, GROUP_CONCAT(kemu ORDER BY kemu) AS kemus, GROUP_CONCAT(fenshu ORDER BY kemu) AS fenshus FROM chengji GROUP BY NAME ) a
SELECT name,kemu,if(fenshu < 80,'合格',if(fenshu >= 90,'优秀','中等')) FROM chengji
一:
Select a.name,a.kemu,a.fenshu,(Select avg(b.fenshu) From t_grade b where a.kemu=b.kemu GROUP BY b.kemu )
From t_grade a Where a.fenshu<(Select avg(b.fenshu) From t_grade b where a.kemu=b.kemu GROUP BY b.kemu ) GROUP BY a.kemu二:
SELECT name,kemu,fenshu from t_grade where name not in(select name from t_grade where fenshu<=80)三:
SELECT
name as 姓名,
MAX(
CASE
WHEN kemu='语文' THEN
fenshu
END
) AS 语文,
MAX(
CASE
WHEN kemu='数学' THEN
fenshu
END
) AS 数学
FROM
t_grade
GROUP BY name
四:
select name,kemu,
case when fenshu>=90 then '优秀'
when fenshu<90 and fenshu>=80 then '中等'
when fenshu<80 then '合格'
end as fenshu
from t_grade
mysql5.6 上验证。
LEFT JOIN
(select kemu,AVG(fenshu) as avg_fenshu FROM chengji GROUP BY kemu) b
on a.kemu = b.kemu
where a.fenshu < b.avg_fenshuselect name,GROUP_CONCAT(fenshu) from chengji where fenshu > 80 GROUP BY NAME HAVING count(*) > 1SELECT
a. NAME,
substring_index(a.fenshus, ',', 1) AS '数学',
substring_index(a.fenshus, ',', - 1) AS '语文'
FROM
(
SELECT
NAME,
GROUP_CONCAT(kemu ORDER BY kemu) AS kemus,
GROUP_CONCAT(fenshu ORDER BY kemu) AS fenshus
FROM
chengji
GROUP BY
NAME
) a
SELECT name,kemu,if(fenshu < 80,'合格',if(fenshu >= 90,'优秀','中等')) FROM chengji