测试数据:-- 课程表 CREATE TABLE Course ( ID NUMBER(4), NAME VARCHAR2(20), ExamineTime DATE );INSERT INTO Course VALUES(1, '数学', to_date('20120301', 'YYYYMMDD')); INSERT INTO Course VALUES(2, '语文', to_date('20120301', 'YYYYMMDD')); INSERT INTO Course VALUES(3, '政治', to_date('20120301', 'YYYYMMDD'));-- 报名表 CREATE TABLE Registe ( ID NUMBER(4), CourseID NUMBER(4), SchoolName VARCHAR2(20), StudentID NUMBER(4) );INSERT INTO Registe VALUES(1, 1, '学校A', '0001'); INSERT INTO Registe VALUES(2, 1, '学校A', '0002'); INSERT INTO Registe VALUES(3, 1, '学校B', '0003'); INSERT INTO Registe VALUES(4, 1, '学校B', '0004');INSERT INTO Registe VALUES(5, 2, '学校A', '0005'); INSERT INTO Registe VALUES(6, 2, '学校A', '0006'); INSERT INTO Registe VALUES(7, 2, '学校B', '0007'); INSERT INTO Registe VALUES(8, 2, '学校B', '0008');INSERT INTO Registe VALUES(9, 3, '学校A', '0009'); INSERT INTO Registe VALUES(10, 3, '学校A', '0010'); INSERT INTO Registe VALUES(11, 3, '学校B', '0011'); INSERT INTO Registe VALUES(12, 3, '学校B', '0012');CREATE VIEW VTemp AS SELECT SchoolName, NAME, COUNT(1) AS n FROM Registe r INNER JOIN Course c ON r.courseid = c.ID GROUP BY SchoolName, NAME; SELECT * FROM VTemp; 测试结果:
--报名表:t1 科目信息表:t2 select t1.学校名称, sum(decode(t2.名称,'数学',1,0)) 数学人数, sum(decode(t2.名称,'语文',1,0)) 语文人数, sum(decode(t2.名称,'英语',1,0)) 英语人数, count(t1.id) 总人数 from t1,t2 where t1.科目信息id=t2.id group by t1.学校名称
CREATE TABLE Course
(
ID NUMBER(4),
NAME VARCHAR2(20),
ExamineTime DATE
);INSERT INTO Course VALUES(1, '数学', to_date('20120301', 'YYYYMMDD'));
INSERT INTO Course VALUES(2, '语文', to_date('20120301', 'YYYYMMDD'));
INSERT INTO Course VALUES(3, '政治', to_date('20120301', 'YYYYMMDD'));-- 报名表
CREATE TABLE Registe
(
ID NUMBER(4),
CourseID NUMBER(4),
SchoolName VARCHAR2(20),
StudentID NUMBER(4)
);INSERT INTO Registe VALUES(1, 1, '学校A', '0001');
INSERT INTO Registe VALUES(2, 1, '学校A', '0002');
INSERT INTO Registe VALUES(3, 1, '学校B', '0003');
INSERT INTO Registe VALUES(4, 1, '学校B', '0004');INSERT INTO Registe VALUES(5, 2, '学校A', '0005');
INSERT INTO Registe VALUES(6, 2, '学校A', '0006');
INSERT INTO Registe VALUES(7, 2, '学校B', '0007');
INSERT INTO Registe VALUES(8, 2, '学校B', '0008');INSERT INTO Registe VALUES(9, 3, '学校A', '0009');
INSERT INTO Registe VALUES(10, 3, '学校A', '0010');
INSERT INTO Registe VALUES(11, 3, '学校B', '0011');
INSERT INTO Registe VALUES(12, 3, '学校B', '0012');CREATE VIEW VTemp AS
SELECT SchoolName, NAME, COUNT(1) AS n
FROM Registe r INNER JOIN Course c
ON r.courseid = c.ID
GROUP BY SchoolName, NAME;
SELECT * FROM VTemp;
测试结果:
--报名表:t1 科目信息表:t2
select t1.学校名称,
sum(decode(t2.名称,'数学',1,0)) 数学人数,
sum(decode(t2.名称,'语文',1,0)) 语文人数,
sum(decode(t2.名称,'英语',1,0)) 英语人数,
count(t1.id) 总人数
from t1,t2
where t1.科目信息id=t2.id
group by t1.学校名称