数据库 查询成绩及格的人

1、建表结构

 

-- 创建表
create table TESTCITY
(
  NAME   VARCHAR2(100),
  RESULT VARCHAR2(100)
);

-- 插入数据
insert into TESTCITY (NAME, RESULT)
values ('a', '正');
insert into TESTCITY (NAME, RESULT)
values ('a', '正');
insert into TESTCITY (NAME, RESULT)
values ('a', '正');
insert into TESTCITY (NAME, RESULT)
values ('a', '负');
insert into TESTCITY (NAME, RESULT)
values ('b', '正');
insert into TESTCITY (NAME, RESULT)
values ('b', '正');
insert into TESTCITY (NAME, RESULT)
values ('b', '正');
insert into TESTCITY (NAME, RESULT)
values ('b', '负');
insert into TESTCITY (NAME, RESULT)
values ('c', '正');
insert into TESTCITY (NAME, RESULT)
values ('d', '正');
insert into TESTCITY (NAME, RESULT)
values ('d', '正');
insert into TESTCITY (NAME, RESULT)
values ('d', '负');
insert into TESTCITY (NAME, RESULT)
values ('d', '负');
commit;

 

如下图：

 

 

-------- 查询结构如下：

 

 

 

select t1.aname as "名字",nvl(t1.zh,0) as "正",nvl(t2.fu,0) as "负",

nvl(t1.zh,0)/(nvl(t2.fu,0)+nvl(t1.zh,0))*100 as "分数"
from (select tc.name aname,count(tc.result)  zh  from testcity tc where tc.result='正' group by tc.name) t1
left join (select tc.name tname,count(tc.result)  fu  from testcity tc where tc.result='负' group by tc.name) t2
on  t1.aname=t2.tname
where nvl(t1.zh,0)/(nvl(t2.fu,0)+nvl(t1.zh,0))*100>=60