一:
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
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
解决方案 »
- struts2 action去除请求后缀,导致jsp、html无法访问
- tomcat5.5中配置jndi的问题
- struts1x里的框架集html:frame如何使用啊?
- 修改url-pattern后出错
- html:form标签会创建actionform到session里?
- 请问java中元数据的使用?
- 怎么样通过一个线程呼叫多个休眠线程的一个线程?
- j2ee中s2sh项目中,查询结果显示在一个table中,怎样可以对查询出来的结果,进行二次筛选?就是在查询结果中进行二次查询?
- 一个没人提过但很关键的问题!!!!!!连接ORACLE的结果集为什么是只读的???
- 如何将double型的变量小数点限定为两位
- sql面试题不会,请各位大神教我
- linux系统中使用tomcat容器java进程自动关闭jvm
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