我有一个这样的桌子报名-
Students Class1 Class2 Class3
student1 1 0 1
student2 0 1 0
student3 1 1 0
studnet4 0 1 1
我想这样输出-
Class1 has 3 Students
Class2 has 2 Students
Class3 has 3 Students
我已经这样查询了-
select classname||' has '||count(num)||' students 'as no_of_students from
(
select * from enroll )
unpivot (
num for classname in (class1,class2,class3)
)
where num=1
group by classname;
但是，如果有更多的课比每次我在in子句中进行更改要多。我也不知道pl / sql。那么，如果有人可以帮助您呢？
-- it is an old-school solution, but it worked for me
-- step 1
create or replace procedure test_students
as
cursor c_cols
is
select column_name
from user_tab_columns
where table_name = 'ENROLL'
and column_name != 'STUDENTS';
l_number_of_students number;
l_my_col user_tab_columns.column_name%type;
l_statement varchar2(30000);
begin
for r_cols in c_cols loop
l_my_col := r_cols.column_name;
l_statement :=
' select sum('||l_my_col||')
from enroll ';
execute immediate l_statement into l_number_of_students;
dbms_output.put_line ('Number of students: '||l_number_of_students ||'in Class :'||l_my_col);
end loop;
end;
-- step 2
begin
test_students;
end;
/
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句