新手上路,望各路高手指教。
现在有一张表名为sheet的表,
student math english chinese music history
TOM 90 80 0 85 0
JERRY 85 70 0 70 0
MARY 70 75 0 0 0
JOHN 90 80 0 65 0
对于全为0的列,不要显示了,即要得到:
student math english music
TOM 90 80 85
JERRY 85 70 70
MARY 70 75 0
JOHN 90 80 65
如何做呢?
现在有一张表名为sheet的表,
student math english chinese music history
TOM 90 80 0 85 0
JERRY 85 70 0 70 0
MARY 70 75 0 0 0
JOHN 90 80 0 65 0
对于全为0的列,不要显示了,即要得到:
student math english music
TOM 90 80 85
JERRY 85 70 70
MARY 70 75 0
JOHN 90 80 65
如何做呢?
如果必须在oracle里写的话,只能用动态sql了.
建成Tom math 90这样的表
或者触发向该表插入记录
这样统计就灵活很多
math english chinese music history
sheet sum_math int;
sum_english int;
sum_chinese int;
sum_music int;
sum_history int;
dy_sql varchar2(5000);
begin
select sum(math) into sum_math
from sheet;
if sum_math = 0 then
dy_sql = dy_syl || ' '
else
dy_sql = dy_sql || 'math, '
end if; ......
......
end;语法大体上如上面所写。