有简单快速的例子提供。高手请留步。
以下是在SQL SERVER中执行没有问题的交叉语句。
create table tb(eirCate varchar(10) ,employeeid varchar(100), sendTime varchar(10) , count int)
insert into tb values('氧气' , '松昌', '2009-06-01' , 74)
insert into tb values('氧气' , '福尔卓', '2009-06-02' , 83)
insert into tb values('氮气' , '常态', '2009-06-03' , 93)
insert into tb values('氮气' , '常态', '2009-06-04' , 74)
insert into tb values('氮气' , '福尔卓', '2009-06-05' , 84)
insert into tb values('氧气' , '常态', '2009-06-05' , 94)
insert into tb values('氧气' , '福尔卓', '2009-06-06' , 94)
insert into tb values('氩气' , '福尔卓', '2009-06-08' , 94)
insert into tb values('氩气' , '松昌', '2009-06-09' , 94)
declare @sql varchar(8000)
set @sql = 'select employeeid '
select @sql = @sql + ' , max(case sendTime when ''' + sendTime + ''' then count else 0 end) [' + sendTime + ']'
from (select distinct sendTime from tb) as a
set @sql = @sql + ' , cast(avg(count*1.0) as decimal(18,2)) 平均数 , sum(count) 总数 from tb group by employeeid'
exec(@sql) 以下是在ORACLE执行的语句。但在转换是发生了错误。.
create table tb(eirCate varchar2(10),employeeid varchar2(100), sendTime varchar2(10) , count int) ;
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氮气' , '常态', '2009-06-04' , 74);
insert into tb values( '氮气' , '福尔卓', '2009-06-05' , 84);
insert into tb values( '氧气' , '常态', '2009-06-05' , 94);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氩气' , '松昌', '2009-06-09' , 94);
select * from tb
declare impl varchar(8000) := 'select employeeid';
begin
select impl := impl || ', max(case sendTime when sendTime then count else 0 end) sendTime '
from (select distinct sendTime from sendInfo) sendInfo;
impl := impl || 'from sendInfo group by employeeid';
dbms_output.put_line(impl);
end;
求和SQL结果一样的ORACLE语句。!!
以下是在SQL SERVER中执行没有问题的交叉语句。
create table tb(eirCate varchar(10) ,employeeid varchar(100), sendTime varchar(10) , count int)
insert into tb values('氧气' , '松昌', '2009-06-01' , 74)
insert into tb values('氧气' , '福尔卓', '2009-06-02' , 83)
insert into tb values('氮气' , '常态', '2009-06-03' , 93)
insert into tb values('氮气' , '常态', '2009-06-04' , 74)
insert into tb values('氮气' , '福尔卓', '2009-06-05' , 84)
insert into tb values('氧气' , '常态', '2009-06-05' , 94)
insert into tb values('氧气' , '福尔卓', '2009-06-06' , 94)
insert into tb values('氩气' , '福尔卓', '2009-06-08' , 94)
insert into tb values('氩气' , '松昌', '2009-06-09' , 94)
declare @sql varchar(8000)
set @sql = 'select employeeid '
select @sql = @sql + ' , max(case sendTime when ''' + sendTime + ''' then count else 0 end) [' + sendTime + ']'
from (select distinct sendTime from tb) as a
set @sql = @sql + ' , cast(avg(count*1.0) as decimal(18,2)) 平均数 , sum(count) 总数 from tb group by employeeid'
exec(@sql) 以下是在ORACLE执行的语句。但在转换是发生了错误。.
create table tb(eirCate varchar2(10),employeeid varchar2(100), sendTime varchar2(10) , count int) ;
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氮气' , '常态', '2009-06-04' , 74);
insert into tb values( '氮气' , '福尔卓', '2009-06-05' , 84);
insert into tb values( '氧气' , '常态', '2009-06-05' , 94);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氩气' , '松昌', '2009-06-09' , 94);
select * from tb
declare impl varchar(8000) := 'select employeeid';
begin
select impl := impl || ', max(case sendTime when sendTime then count else 0 end) sendTime '
from (select distinct sendTime from sendInfo) sendInfo;
impl := impl || 'from sendInfo group by employeeid';
dbms_output.put_line(impl);
end;
求和SQL结果一样的ORACLE语句。!!
begin
select impl || ', max(case sendTime when '''||sendTime||''' then count else 0 end) sendTime ' into impl
from (select distinct sendTime from sendInfo) sendInfo;
impl := impl || 'from sendInfo group by employeeid';
dbms_output.put_line(impl);
end;
可以用游标来实现输出
impl varchar(4000) := 'select employeeid';
v_time sendInfo.sendTime%type;
cursor v_cur is select distinct sendTime from sendInfo;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || 'from sendInfo group by employeeid';
dbms_output.put_line(impl);
end;
/