表A:id name desc
001 name001 desc001
002 name002 desc002
003 name003 desc003
表B:id AId flag usercode bDesc handdate
1 001 0 user001 desc1 2010-1-1
2 001 1 user002 desc2 2010-1-2
3 001 2 user003 desc3 2010-1-3
4 002 0 user004 desc4 2010-1-4
2 002 1 user002 desc2 2010-1-2
4 003 0 user004 desc4 2010-1-4
A.id 关联B.AId,flag是状态标志,共有3个:0,1,2;要得到查询结果:id name desc usercode0 bDesc0 handdate0 usercode1 bDesc1 handdate1 usercode2 bDesc2 handdate2
001 name001 desc001 user001 desc1 2010-1-1 user002 desc2 2010-1-2 user003 desc3 2010-1-3
002 name002 desc002 user003 desc3 2010-1-3 user002 desc2 2010-1-2
003 name003 desc003 user004 desc4 2010-1-4如何处理,
001 name001 desc001
002 name002 desc002
003 name003 desc003
表B:id AId flag usercode bDesc handdate
1 001 0 user001 desc1 2010-1-1
2 001 1 user002 desc2 2010-1-2
3 001 2 user003 desc3 2010-1-3
4 002 0 user004 desc4 2010-1-4
2 002 1 user002 desc2 2010-1-2
4 003 0 user004 desc4 2010-1-4
A.id 关联B.AId,flag是状态标志,共有3个:0,1,2;要得到查询结果:id name desc usercode0 bDesc0 handdate0 usercode1 bDesc1 handdate1 usercode2 bDesc2 handdate2
001 name001 desc001 user001 desc1 2010-1-1 user002 desc2 2010-1-2 user003 desc3 2010-1-3
002 name002 desc002 user003 desc3 2010-1-3 user002 desc2 2010-1-2
003 name003 desc003 user004 desc4 2010-1-4如何处理,
--用decode()
select a.id,
name,
desc,--这个是oracle关键字 最好不用作为列名
max(decode(flag,0,usercode,null)) usercode0,
max(decode(flag,0,bDesc,null)) bDesc0,
max(decode(flag,0,handdate,null)) handdate0,
max(decode(flag,1,usercode,null)) usercode1,
max(decode(flag,1,bDesc,null)) bDesc1,
max(decode(flag,1,handdate,null)) handdate1,
max(decode(flag,2,usercode,null)) usercode2,
max(decode(flag,2,bDesc,null)) bDesc2,
max(decode(flag,2,handdate,null)) handdate2
from a,b
where a.id=b.id
报不是单组分组函数
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select * from taba;ID NAME ADESC
------ -------------- --------------
001 name001 desc001
002 name002 desc002
003 name003 desc003SQL> select * from tabb; ID AID FLAG USERCODE BDESC HANDDATE
---------- ------ ---------- -------------- ---------- ----------
1 001 0 user001 desc1 2010-01-01
2 001 1 user002 desc2 2010-01-02
3 001 2 user003 desc3 2010-01-03
4 002 0 user003 desc3 2010-01-04
2 002 1 user002 desc2 2010-01-02
4 003 0 user004 desc4 2010-01-046 rows selected.SQL> --行列转换过程:
SQL> editcreate or replace procedure row_to_col_func3(cur out sys_refcursor)
as
sqlstr varchar2(2000):='select a.id,max(a.name) name,max(adesc) adesc';
begin
for rs in (select distinct flag from tabb ) loop
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.usercode,'''')) '||'usercode'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.bdesc,'''')) '||'bdesc'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.handdate,'''')) '||'handdate'||rs.flag;
end loop ;
sqlstr:=sqlstr||chr(10)||'from taba a,tabb b where a.id=b.aid group by a.id ' ;
open cur for sqlstr;
end row_to_col_func3;SQL> /Procedure created.SQL> var cur refcursor
SQL> exec row_to_col_func3(:cur);PL/SQL procedure successfully completed.SQL> print curID NAME ADESC USERCODE0 BDESC0 HANDDATE0 USERCODE1 BDESC1 HANDDATE1 USERCODE2 BDESC2 HANDDATE2
------ --------- --------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ------- ---------
001 name001 desc001 user001 desc1 2010.01.01 user002 desc2 2010.01.02 user003 desc3 2010.01.03
002 name002 desc002 user003 desc3 2010.01.04 user002 desc2 2010.01.02
003 name003 desc003 user004 desc4 2010.01.04 SQL> [/code]
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select * from taba;ID NAME ADESC
------ -------------- --------------
001 name001 desc001
002 name002 desc002
003 name003 desc003SQL> select * from tabb; ID AID FLAG USERCODE BDESC HANDDATE
---------- ------ ---------- -------------- ---------- ----------
1 001 0 user001 desc1 2010-01-01
2 001 1 user002 desc2 2010-01-02
3 001 2 user003 desc3 2010-01-03
4 002 0 user003 desc3 2010-01-04
2 002 1 user002 desc2 2010-01-02
4 003 0 user004 desc4 2010-01-046 rows selected.SQL> --行列转换过程:
SQL> editcreate or replace procedure row_to_col_func3(cur out sys_refcursor)
as
sqlstr varchar2(2000):='select a.id,max(a.name) name,max(adesc) adesc';
begin
for rs in (select distinct flag from tabb ) loop
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.usercode,'''')) '||'usercode'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.bdesc,'''')) '||'bdesc'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.handdate,'''')) '||'handdate'||rs.flag;
end loop ;
sqlstr:=sqlstr||chr(10)||'from taba a,tabb b where a.id=b.aid group by a.id ' ;
open cur for sqlstr;
end row_to_col_func3;SQL> /Procedure created.SQL> var cur refcursor
SQL> exec row_to_col_func3(:cur);PL/SQL procedure successfully completed.SQL> print curID NAME ADESC USERCODE0 BDESC0 HANDDATE0 USERCODE1 BDESC1 HANDDATE1 USERCODE2 BDESC2 HANDDATE2
------ --------- --------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ------- ---------
001 name001 desc001 user001 desc1 2010.01.01 user002 desc2 2010.01.02 user003 desc3 2010.01.03
002 name002 desc002 user003 desc3 2010.01.04 user002 desc2 2010.01.02
003 name003 desc003 user004 desc4 2010.01.04 SQL> [/code]
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select * from taba;ID NAME ADESC
------ -------------- --------------
001 name001 desc001
002 name002 desc002
003 name003 desc003SQL> select * from tabb; ID AID FLAG USERCODE BDESC HANDDATE
---------- ------ ---------- -------------- ---------- ----------
1 001 0 user001 desc1 2010-01-01
2 001 1 user002 desc2 2010-01-02
3 001 2 user003 desc3 2010-01-03
4 002 0 user003 desc3 2010-01-04
2 002 1 user002 desc2 2010-01-02
4 003 0 user004 desc4 2010-01-046 rows selected.SQL> --行列转换过程:
SQL> editcreate or replace procedure row_to_col_func3(cur out sys_refcursor)
as
sqlstr varchar2(2000):='select a.id,max(a.name) name,max(adesc) adesc';
begin
for rs in (select distinct flag from tabb ) loop
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.usercode,'''')) '||'usercode'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.bdesc,'''')) '||'bdesc'||rs.flag;
sqlstr:=sqlstr||chr(10)||','||'max(decode(b.flag,'''||rs.flag||''',b.handdate,'''')) '||'handdate'||rs.flag;
end loop ;
sqlstr:=sqlstr||chr(10)||'from taba a,tabb b where a.id=b.aid group by a.id ' ;
open cur for sqlstr;
end row_to_col_func3;SQL> /Procedure created.SQL> var cur refcursor
SQL> exec row_to_col_func3(:cur);PL/SQL procedure successfully completed.SQL> print curID NAME ADESC USERCODE0 BDESC0 HANDDATE0 USERCODE1 BDESC1 HANDDATE1 USERCODE2 BDESC2 HANDDATE2
------ --------- --------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ------- ---------
001 name001 desc001 user001 desc1 2010.01.01 user002 desc2 2010.01.02 user003 desc3 2010.01.03
002 name002 desc002 user003 desc3 2010.01.04 user002 desc2 2010.01.02
003 name003 desc003 user004 desc4 2010.01.04 SQL> [/code]
create table ta(id varchar(10),[name] varchar(20),[desc] varchar(20))
insert into ta
select '001', 'name001', 'desc001' union all
select '002', 'name002', 'desc002' union all
select '003', 'name003', 'desc003'
go
create table tb(id int,AId varchar(10),flag int,usercode varchar(10),bDesc varchar(10),handdate datetime)
insert into tb
select 1, '001', 0, 'user001', 'desc1', '2010-1-1' union all
select 2, '001', 1, 'user002', 'desc2', '2010-1-2' union all
select 3, '001', 2, 'user003', 'desc3', '2010-1-3' union all
select 4, '002', 0, 'user004', 'desc4', '2010-1-4' union all
select 2, '002', 1, 'user002', 'desc2', '2010-1-2' union all
select 4, '003', 0, 'user004', 'desc4', '2010-1-4'
godeclare @sql varchar(max)
set @sql = 'select a.id,a.[name],a.[desc]'
select @sql = @sql + ',max(case flag when ''' + ltrim(flag) + ''' then usercode else '''' end)[usercode' + ltrim(flag) + ']'
+ ',max(case flag when ''' + ltrim(flag) + ''' then bDesc else '''' end)[bDesc' + ltrim(flag) + ']'
+ ',max(case flag when ''' + ltrim(flag) + ''' then convert(varchar(10),handdate,120) else '''' end)[handdate' + ltrim(flag) + ']'
from(select distinct flag from tb)u
select @sql = @sql + ' from ta a left join tb b on a.id = b.aid group by a.id,a.[name],a.[desc]'
exec(@sql)drop table ta,tb
id name desc usercode0 bDesc0 handdate0 usercode1 bDesc1 handdate1 usercode2 bDesc2 handdate2
---------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
001 name001 desc001 user001 desc1 2010-01-01 user002 desc2 2010-01-02 user003 desc3 2010-01-03
002 name002 desc002 user004 desc4 2010-01-04 user002 desc2 2010-01-02
003 name003 desc003 user004 desc4 2010-01-04 (3 行受影响)
[/code]
--这个是固定的,那么执行分组+decode就够了
--如果不固定,就用我上面的存储过程动态转换select a.id,max(a.name) name,max(adesc) adesc
,max(decode(b.flag,'0',b.usercode,'')) usercode0
,max(decode(b.flag,'0',b.bdesc,'')) bdesc0
,max(decode(b.flag,'0',b.handdate,'')) handdate0
,max(decode(b.flag,'1',b.usercode,'')) usercode1
,max(decode(b.flag,'1',b.bdesc,'')) bdesc1
,max(decode(b.flag,'1',b.handdate,'')) handdate1
,max(decode(b.flag,'2',b.usercode,'')) usercode2
,max(decode(b.flag,'2',b.bdesc,'')) bdesc2
,max(decode(b.flag,'2',b.handdate,'')) handdate2
from taba a,tabb b where a.id=b.aid
group by a.id
ID NAME ADESC USERCODE0 BDESC0 HANDDATE0 USERCODE1 BDESC1 HANDDATE1 USERCODE2 BDESC2 HANDDATE2
------ --------- --------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ------- ---------
001 name001 desc001 user001 desc1 2010.01.01 user002 desc2 2010.01.02 user003 desc3 2010.01.03
002 name002 desc002 user003 desc3 2010.01.04 user002 desc2 2010.01.02
003 name003 desc003 user004 desc4 2010.01.04
[/code]
create table ta(id varchar(10),[name] varchar(20),[desc] varchar(20))
insert into ta
select '001', 'name001', 'desc001' union all
select '002', 'name002', 'desc002' union all
select '003', 'name003', 'desc003'
go
create table tb(id int,AId varchar(10),flag int,usercode varchar(10),bDesc varchar(10),handdate datetime)
insert into tb
select 1, '001', 0, 'user001', 'desc1', '2010-1-1' union all
select 2, '001', 1, 'user002', 'desc2', '2010-1-2' union all
select 3, '001', 2, 'user003', 'desc3', '2010-1-3' union all
select 4, '002', 0, 'user004', 'desc4', '2010-1-4' union all
select 2, '002', 1, 'user002', 'desc2', '2010-1-2' union all
select 4, '003', 0, 'user004', 'desc4', '2010-1-4'
go declare @sql varchar(max)
set @sql = 'select a.id,a.[name],a.[desc]'
select @sql = @sql + ',max(case flag when ''' + ltrim(flag) + ''' then usercode else '''' end)[usercode' + ltrim(flag) + ']'
+ ',max(case flag when ''' + ltrim(flag) + ''' then bDesc else '''' end)[bDesc' + ltrim(flag) + ']'
+ ',max(case flag when ''' + ltrim(flag) + ''' then convert(varchar(10),handdate,120) else '''' end)[handdate' + ltrim(flag) + ']'
from(select distinct flag from tb)u
select @sql = @sql + ' from ta a left join tb b on a.id = b.aid group by a.id,a.[name],a.[desc]'
exec(@sql) drop table ta,tb
id name desc usercode0 bDesc0 handdate0 usercode1 bDesc1 handdate1 usercode2 bDesc2 handdate2
---------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
001 name001 desc001 user001 desc1 2010-01-01 user002 desc2 2010-01-02 user003 desc3 2010-01-03
002 name002 desc002 user004 desc4 2010-01-04 user002 desc2 2010-01-02
003 name003 desc003 user004 desc4 2010-01-04 (3 行受影响)
[/code]
,max(decode(b.flag,'0',b.usercode,'')) usercode0
,max(decode(b.flag,'0',b.bdesc,'')) bdesc0
,max(decode(b.flag,'0',b.handdate,'')) handdate0
,max(decode(b.flag,'1',b.usercode,'')) usercode1
,max(decode(b.flag,'1',b.bdesc,'')) bdesc1
,max(decode(b.flag,'1',b.handdate,'')) handdate1
,max(decode(b.flag,'2',b.usercode,'')) usercode2
,max(decode(b.flag,'2',b.bdesc,'')) bdesc2
,max(decode(b.flag,'2',b.handdate,'')) handdate2
from ta a,tb b where a.id=b.aid
group by a.id测试过 没问题
SQL> SELECT a.id,a.NAME,a.DES,replace(wm_concat(b.usercode||' '||b.Bdesc||' '||B
.handdate),',',' ')FROM a,b WHERE a.ID=b.AID GROUP BY a.id,a.NAME,a.DES;ID NAME DES
--- ---------- ----------
REPLACE(WM_CONCAT(B.USERCODE||''||B.BDESC||''||B.HANDDATE),',','')
--------------------------------------------------------------------------------001 name001 desc001
user001 desc1 2010-1-1 user002 desc2 2010-1-2 user003 desc3 2010-1-3002 name002 desc002
user004 desc4 2010-1-4 user002 desc2 2010-1-2003 name003 desc003
user004 desc4 2010-1-4