sql查询请教 有这样一张表张三 数学 90张三 语文 75李四 数学 70李四 语文 95王五 数学 99王五 语文 90王五 英语 100要用一条语句查询出所有科目都唱过80分的学生名字。请高手赐教,急。谢谢。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select distinct name from tempminusselect distinct name from temp where fsh < 80 你提供的信息不够足,是不是有个学科的字典还是我根据你的表distinct学科就是字典吗要不没法写 select namefrom (select name,count(course) t1,count(case when score>80 then 1 else 0 end) t2from table_namegroup by name)where t1=t2 select namefrom (select name,count(course) t1,sum(case when score>80 then 1 else 0 end) t2from table_namegroup by name)where t1=t2 SELECT DISTINCT(t1.xm) FROM test111 t1WHERE t1.xm NOT IN ( SELECT t2.xm FROM test111 t2 WHERE t2.cj <= 80)看这个咋样 这样:假设该表为Table 有3列:姓名、科目、分数。 可以使用以下sql满足你的要求。select 姓名,count(1) from Table where 分数>=80 group by 姓名 having count(1)=科目数(这是个数字,有几科就等于几) Phoenix_99 的解答:select distinct name from tempminusselect distinct name from temp where fsh < 80;这才是高手的解答,用集合的方式思考问题!高手啊! select name from table group by name having min(score)>80 minitoy 的解答最好:select name from table group by name having min(score)>80; Minitoy 膜拜一个~!select distinct t1.name form tmp_table t1where not exists(select * from tmp_table t2where t1.name=t2.name and t2.score<80); select distinct(s1.name) from s s1 where not exists ( select score from s s2 where s1.name=s2.name and s2.score<80) ORA-01693错误 怎么在执行大数据流量的脚本的时候提示他正在执行? 请教一下oracle的即时客户端应该怎么配置 表结构中VARCHAR2(4000)最多只能存2000字符? 关于oracledbconsole 我在sqlplus里insert了一条数据,在sqlplus可以select看到,为什么在worksheet和里select看不到? 关于数组的一个问题。 急救,WIN2000群集做好了,现在如何做ORACLE群集?? 请问判断系统时间与某字段时间的差值? ★★怎么获得计算机名称或者IP地址★★ 根据时间字段,统计 相邻记录,时间间隔大于半小时的记录数,在线等 求分页展现逻辑
minus
select distinct name from temp where fsh < 80
还是我根据你的表distinct学科就是字典吗
要不没法写
from
(select name,count(course) t1,count(case when score>80 then 1 else 0 end) t2
from table_name
group by name)
where t1=t2
from
(select name,count(course) t1,sum(case when score>80 then 1 else 0 end) t2
from table_name
group by name)
where t1=t2
FROM test111 t1
WHERE t1.xm NOT IN (
SELECT t2.xm
FROM test111 t2
WHERE t2.cj <= 80
)看这个咋样
where 分数>=80 group by 姓名 having count(1)=科目数(这是个数字,有几科就等于几)
select distinct name from temp
minus
select distinct name from temp where fsh < 80;这才是高手的解答,用集合的方式思考问题!高手啊!
select name from table group by name having min(score)>80;
select distinct t1.name
form tmp_table t1
where not exists(
select * from tmp_table t2
where t1.name=t2.name and t2.score<80);
where not exists (
select score from s s2 where s1.name=s2.name and s2.score<80
)