create table report_table( id varchar2(3), name varchar2(30) );insert into report_table(id,name) values('001','jack'); insert into report_table(id,name) values('002',rose);select rpt.id,rpt.name,t1.columnName from report_table rpt left join T1 on rpt.id = T1.id; 根据你的逻辑,考虑是否需要添加聚集函数!
DB2 可以用这个 SQL 生成数据,但不知道 Oracle 有没有类似的方法。with a (ID) as ( select cast(1 as integer) ID from sysibm.sysdummy1 union all select ID + 1 from a where I < 1000 ) select ID from a
原来 Oracle connect by 也能做到类似的递归。with 客户资料 as ( select trim(to_char(rownum, '00000000')) as 客户 from dual connect by rownum <= 9 ) , 订阅资料 as ( select '00000001' as 客户, '南方周末' as 报纸 from dual union all select '00000001' as 客户, '南方都市报' as 报纸 from dual union all select '00000003' as 客户, '南方都市报' as 报纸 from dual union all select '00000005' as 客户, '计算机世界' as 报纸 from dual) select 客户资料.客户, 订阅资料.报纸 from 客户资料 left join 订阅资料 on 客户资料.客户 = 订阅资料.客户 order by 客户资料.客户 ;
大哥,T1是你数据库里面建立的Table啊!我没有写出来,因为你的问题里面已经包含了! create table report_table( id varchar2(3), name varchar2(30) );create table T1( id varchar2(3), name varchar2(30) );insert into report_table(id,name) values('001','jack'); insert into report_table(id,name) values('002','rose');insert into T1(id,name) values('001','jack');select rpt.id,rpt.name,t1.columnName from report_table rpt left join T1 on rpt.id = T1.id; 这下可以了。
create table report_table(
id varchar2(3),
name varchar2(30)
);insert into report_table(id,name)
values('001','jack');
insert into report_table(id,name)
values('002',rose);select rpt.id,rpt.name,t1.columnName from
report_table rpt
left join T1 on rpt.id = T1.id;
根据你的逻辑,考虑是否需要添加聚集函数!
楼主的想法很好,因为在程序里面写可能要通过硬编码的方式。
要是通过SQL来写,我之前的回复(第二个insert语句需要改一下,对'rose'加引号)应该可以满足你的需求,这就要求一定要在数据库里面维护报表要显示的有哪些!否则,就要采取楼主不愿意采用的方式了。
最后的SQL语句执行不了,T1没有指定表名字。
select cast(1 as integer) ID from sysibm.sysdummy1
union all
select ID + 1 from a where I < 1000
)
select ID
from a
select trim(to_char(rownum, '00000000')) as 客户
from dual
connect by rownum <= 9
)
, 订阅资料 as (
select '00000001' as 客户, '南方周末' as 报纸 from dual
union all
select '00000001' as 客户, '南方都市报' as 报纸 from dual
union all
select '00000003' as 客户, '南方都市报' as 报纸 from dual
union all
select '00000005' as 客户, '计算机世界' as 报纸 from dual)
select 客户资料.客户, 订阅资料.报纸
from 客户资料
left join 订阅资料 on 客户资料.客户 = 订阅资料.客户
order by 客户资料.客户
;
大哥,T1是你数据库里面建立的Table啊!我没有写出来,因为你的问题里面已经包含了!
create table report_table(
id varchar2(3),
name varchar2(30)
);create table T1(
id varchar2(3),
name varchar2(30)
);insert into report_table(id,name)
values('001','jack');
insert into report_table(id,name)
values('002','rose');insert into T1(id,name)
values('001','jack');select rpt.id,rpt.name,t1.columnName from
report_table rpt
left join T1 on rpt.id = T1.id;
这下可以了。