sum(decode(,'',))怎么对付两个字段?
字段:形状 Shape,颜色 colour
形状有100种,颜色有50种
select date,sum(decode(Shape,'A1',money))
from company
group by date
可以算出Shape为'A1'的money。
现在要同时按照“形状 Shape,颜色 colour”统计汇总,怎么写SQL语句?最好只用查询!
我可不想把每种Shape,colour都写出来,而且希望能够不统计不存在的Shape和colour!
字段:形状 Shape,颜色 colour
形状有100种,颜色有50种
select date,sum(decode(Shape,'A1',money))
from company
group by date
可以算出Shape为'A1'的money。
现在要同时按照“形状 Shape,颜色 colour”统计汇总,怎么写SQL语句?最好只用查询!
我可不想把每种Shape,colour都写出来,而且希望能够不统计不存在的Shape和colour!
select date,sum(money)
from company
group by date,shape,colour
一般DCODE用在行列转换的比较多
楼主,应该是统计存在的shape和colour吧!试下如下命令:
select date,sum(decode(Shape,'A1',money))
from company
group by date,shape,colour
select date,
sum(money)
from company
group by date,
shape,
colour;
from t1 group by ID;
--合计
select date,shape,colour,sum(money)
from company
group by date,shape,colour;
from company
group by date, Shape, colour
我当然知道可以这么写,但是这么写的语句必须导入Excel中生成数据表,再做成数据透视表,才是我们需要的资料。
我想不用数据透视表,直接生成我们需要的资料。
如果生成的数据表要做成数据透视表,就很没有面子了!
http://blog.csdn.net/chqchq/archive/2008/05/22/2468980.aspx
普通行列转换
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/我运行代码出错:
SQL> declare @sql varchar(8000)
2 set @sql = 'select Name as ' + '姓名'
3 select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
4 from (select distinct Subject from tb) as a
5 set @sql = @sql + ' from tb group by name'
6 exec(@sql)
7 /
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
ORA-06550: 第 2 行, 第 9 列:
PLS-00103: 出现符号 "@"在需要下列之一时:
begin function
package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
你想问的是不是 oracle 不确定列的行列转换?
--测试数据
create table t (XH varchar2(10), DDATE date, SXF int);
insert into t select 1,sysdate,10 from dual union all
select 1,sysdate+1,14 from dual union all
select 1,sysdate+2,23 from dual union all
select 2,sysdate,21 from dual union all
select 2,sysdate+1,24 from dual union all
select 3,sysdate,13 from dual union all
select 3,sysdate+1,22 from dual;
--
create or replace package sp_test
is
type ResultData is ref cursor;
procedure getRstData( rst out ResultData);
end sp_test;
/
create or replace package body sp_test
is
procedure getRstData( rst out ResultData)
is
begin
declare
cursor cur is select distinct (DDATE) from t;
tmp_ddate date;
str varchar2(4000);
begin
str:='select xh';
open cur;
loop fetch cur into tmp_ddate;
exit when cur%notfound;
str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"';
end loop;
str:=str||' from t group by xh';
-- dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
end sp_test;
/
--输出结果
1 10 14 23
2 21 24 0
3 13 22 0