另一个帖子的地址:
http://community.csdn.net/Expert/topic/3217/3217086.xml?temp=.7613031我问了一下,有人提示说可以用两个while循环实现:
先从member表里查出人名(while),再从project表里查出相应的project(while),此时member和project在表一中对应的记录为唯一的,可以查询出来,既利用循环一次查一条记录。但他说的不是很清楚,请大家帮忙!!!
解决马上揭贴,在线等…………
http://community.csdn.net/Expert/topic/3217/3217086.xml?temp=.7613031我问了一下,有人提示说可以用两个while循环实现:
先从member表里查出人名(while),再从project表里查出相应的project(while),此时member和project在表一中对应的记录为唯一的,可以查询出来,既利用循环一次查一条记录。但他说的不是很清楚,请大家帮忙!!!
解决马上揭贴,在线等…………
用存储过程处理吧
---------- ----------
A aaa
A aab
A aac
B bba
B bbb
B bbc
B bbd
C ccc已选择8行。已用时间: 00: 00: 00.47
11:20:30 SQL> declare
11:20:35 2 v_sql varchar2(1000);
11:20:35 3 cursor c_1 is select distinct col2 from tb;
11:20:35 4 begin
11:20:35 5 for c in c_1 loop
11:20:35 6 v_sql:=v_sql||'sum(decode(col2,'''||c.col2||''',1,0)),';
11:20:35 7 end loop;
11:20:35 8 v_sql:='select '||substr(v_sql,1,length(v_sql)-1)||' from tb group by col1';
11:20:37 9 dbms_output.put_line(v_sql);
11:20:41 10 end;
11:20:41 11 /
select
sum(decode(col2,'aaa',1,0)),sum(decode(col2,'aab',1,0)),sum(decode(col2,'aac',1,
0)),sum(decode(col2,'bba',1,0)),sum(decode(col2,'bbb',1,0)),sum(decode(col2,'bbc
',1,0)),sum(decode(col2,'bbd',1,0)),sum(decode(col2,'ccc',1,0)) from tb group by
col1PL/SQL 过程已成功完成。已用时间: 00: 00: 00.31
11:20:41 SQL> select
11:20:46 2 sum(decode(col2,'aaa',1,0)),sum(decode(col2,'aab',1,0)),sum(decode(col2,'aac',1
11:20:46 3 0)),sum(decode(col2,'bba',1,0)),sum(decode(col2,'bbb',1,0)),sum(decode(col2,'bb
11:20:46 4 ',1,0)),sum(decode(col2,'bbd',1,0)),sum(decode(col2,'ccc',1,0)) from tb group b
11:20:47 5 col1;SUM(DECODE(COL2,'AAA',1,0)) SUM(DECODE(COL2,'AAB',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'AAC',1,0)) SUM(DECODE(COL2,'BBA',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBB',1,0)) SUM(DECODE(COL2,'BBC',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBD',1,0)) SUM(DECODE(COL2,'CCC',1,0))
--------------------------- ---------------------------
1 1
1 0
0 0
0 0
SUM(DECODE(COL2,'AAA',1,0)) SUM(DECODE(COL2,'AAB',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'AAC',1,0)) SUM(DECODE(COL2,'BBA',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBB',1,0)) SUM(DECODE(COL2,'BBC',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBD',1,0)) SUM(DECODE(COL2,'CCC',1,0))
--------------------------- ---------------------------
0 0
0 1
1 0
1 0
SUM(DECODE(COL2,'AAA',1,0)) SUM(DECODE(COL2,'AAB',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'AAC',1,0)) SUM(DECODE(COL2,'BBA',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBB',1,0)) SUM(DECODE(COL2,'BBC',1,0))
--------------------------- ---------------------------
SUM(DECODE(COL2,'BBD',1,0)) SUM(DECODE(COL2,'CCC',1,0))
--------------------------- ---------------------------
0 0
0 0
0 0
0 1
已用时间: 00: 00: 00.25
11:20:49 SQL>
通过这个语句就可以得到需要的报表了。
bzszp(www.bzszp.533.net) ,谢谢你,傍晚来给分。谢谢
谢谢,虽然暂时还没有解决问题。请到这个帖子里来,我给分。
http://community.csdn.net/Expert/topic/3217/3217086.xml?temp=.7613031
参考此贴中我的回复:http://community.csdn.net/Expert/topic/3221/3221048.xml?temp=.0534479
go
create table TABLE1(memname varchar(10), PROJECT varchar(10),manhour int)
go
insert into table1 select '人员1','工程1',23
union all select '人1','工程2',10
union all select '人2','工程1',10
union all select '人3','工程2',15
union all select '人4','工程3',50
declare @sql varchar(8000)select @sql = ' select memname'
select @sql = @sql + ',sum(case PROJECT when '''+PROJECT+''' then manhour else 0 end) ['+PROJECT+']'
from (select distinct PROJECT from table1 ) as a
select @sql = @sql+',sum(manhour) as 合计 from table1 group by memname'exec(@sql)/*
memname 工程1 工程2 工程3 合计
---------- ----------- ----------- ----------- -----------
人1 0 10 0 10
人2 10 0 0 10
人3 0 15 0 15
人4 0 0 50 50
人员1 23 0 0 23*/
txlicenhe(马可) 谢谢,我先看看
declare @s varchar(8000)
set @s='select MEMNAME'
select @s=@s+',['+PROJECT+']=sum(case PROJECT when '''+PROJECT+''' then MANHOUR end)'
from 表 group by PROJECT order by PROJECT
set @s= @s+' from 表 group by MEMNAME'
exec(@s)
set @sql = 'select menname'
select @sql = @sql + ',sum(case project when '''+project+''' then manhour end)
['+project+']'
from (select distinct project from 表 ) as aselect @sql = @sql+', sum(manhour) as 合計 from 表 group by menname'
exec(@sql)