select yymm, sum(decode(d1,'N',1,0))+ sum(decode(d2,'N',1,0))+ sum(decode(d3,'N',1,0))+ sum(decode(d4,'N',1,0))+ sum(decode(d5,'N',1,0))+ sum(decode(d6,'N',1,0))+ sum(decode(d7,'N',1,0)) from t group by yymm
--测试数据 create table t(YYMM varchar2(10),D1 varchar2(10),D2 varchar2(10),D3 varchar2(10),D4 varchar2(10),D5 varchar2(10),D6 varchar2(10),D7 varchar2(10) ); insert into t select '2007-07','Y','Y','N','N','Y','Y','Y' from dual union all select '2007-08','Y','N','Y','Y','Y','Y','Y' from dual; --create funcion create or replace function sum_string(v_sql varchar2) return varchar2 as type cur_alldata is ref cursor; l_alldata cur_alldata; v_row varchar2(99); v_sum varchar2(3999); begin open l_alldata for v_sql; loop fetch l_alldata into v_row; exit when l_alldata%notfound; v_sum := v_sum||'sum(decode('||v_row||',''N'',1,0))+'; end loop; v_sum := substr(v_sum,1,length(v_sum)-1); close l_alldata; return v_sum; end;--测试 select yymm,sum_string('select column_name from user_tab_columns where table_name=upper(''t'') and column_name<>upper(''yymm'')') from t group by yymm; --显示结果 2007-07 2 2007-08 1
SELECT YYMM, SUM(LENGTHB(REPLACE(DECODE(D1, 'Y', 0, 1) || DECODE(D2, 'Y', 0, 1) || DECODE(D3, 'Y', 0, 1) || DECODE(D4, 'Y', 0, 1) || DECODE(D5, 'Y', 0, 1) || DECODE(D6, 'Y', 0, 1) || DECODE(D7, 'Y', 0, 1), '0',''))) AS COUNT FROM T GROUP BY YYMM要找出哪个选定的日期, 再加个WHERE就行了 另:如果YYMM是唯一KEY的话, 那么不用SUM和GROUP BY也行
sum(decode(d1,'N',1,0))+
sum(decode(d2,'N',1,0))+
sum(decode(d3,'N',1,0))+
sum(decode(d4,'N',1,0))+
sum(decode(d5,'N',1,0))+
sum(decode(d6,'N',1,0))+
sum(decode(d7,'N',1,0))
from t group by yymm
create table t(YYMM varchar2(10),D1 varchar2(10),D2 varchar2(10),D3 varchar2(10),D4 varchar2(10),D5 varchar2(10),D6 varchar2(10),D7 varchar2(10) );
insert into t
select '2007-07','Y','Y','N','N','Y','Y','Y' from dual union all
select '2007-08','Y','N','Y','Y','Y','Y','Y' from dual;
--create funcion
create or replace function sum_string(v_sql varchar2)
return varchar2
as
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
v_sum := v_sum||'sum(decode('||v_row||',''N'',1,0))+';
end loop;
v_sum := substr(v_sum,1,length(v_sum)-1);
close l_alldata;
return v_sum;
end;--测试
select yymm,sum_string('select column_name from user_tab_columns where table_name=upper(''t'') and column_name<>upper(''yymm'')') from t group by yymm;
--显示结果
2007-07 2
2007-08 1
DECODE(D2, 'Y', 0, 1) ||
DECODE(D3, 'Y', 0, 1) ||
DECODE(D4, 'Y', 0, 1) ||
DECODE(D5, 'Y', 0, 1) ||
DECODE(D6, 'Y', 0, 1) ||
DECODE(D7, 'Y', 0, 1), '0',''))) AS COUNT
FROM T
GROUP BY YYMM要找出哪个选定的日期, 再加个WHERE就行了
另:如果YYMM是唯一KEY的话, 那么不用SUM和GROUP BY也行