比如表a 有id,name,time 表b,c,d也一样有相同字段,如何把他们的所有数据按name显示出来,但每个表的数据行数是不一样的
可以根据id或则name关联。在线等……
可以根据id或则name关联。在线等……
解决方案 »
- oracle 10g安装问题 在oraparam.ini中未找到先决条件检查
- 数据类型number(20,4)强制转换varchar
- 如何在oracle中循环接受数据
- 归档日志问题
- Windows dos下倒库Oracle消息语言如何设置为中文?
- 如何通过递归求总公司"100001"下面的所有公司?
- 救救我吧!!!!
- 初级的问题--PL/sql 中写if 等逻辑判断?
- Oracle的有没有连接和查询的客户端工具?
- Oracle中的having问题
- ORACLE中的SQL语句语法错误(使用sql developer将sqlserver转换到oracle).
- 问下, odbc oracle ,存储过程怎么执行???调什么函数~~~~~~~~
a有 b有 c有
name id time name id time name id time
深圳 1 东莞 2 可能有6条数据
东莞 2如何显示
深圳 a的time b的time c的time
东莞
佛山
max(case tab when 'b' then time) b的time,
max(case tab when 'c' then time) c的time,
max(case tab when 'd' then time) d的time,
from (select 'a' tab,name,id,time from a
union all
select 'b' tab,name,id,time from b
union all
select 'c' tab,name,id,time from c
union all
select 'd' tab,name,id,time from d)
group by name;
max(case tab when 'b' then b.bTime) bTime,
max(case tab when 'c' then c.cTime) cTime,
max(case tab when 'd' then d.dTime) dTime from(
(select 'a' tab,a.c_time as aTime,b.name,a.superior_id as id from(
select sum((p.sende_time-f.county_time)/decode(code.image_count,0,1)) as
group by tb.superior_id)a,t_branch b where a.superior_id=b.id)a union all
(select 'b' tab,b.time/b.img_count as bTime,c.name,b.id from(
select sum((p.sende_time-code.create_time))time,sum(code.image_count)as img_count,tb.id fromgroup by tb.id)b,t_branch c where b.id=c.id(+))b union all
(select 'c' tab,c.time/img_count as cTime,b.name,c.id from(
select sum(p.sende_time-ba.bar_return_time)time,sum(code.image_count) as img_count,tb.id fromgroup by tb.id)c,t_branch b where c.id=b.id(+))c union all
(select 'd'tab,l.time/l.img_count as dTime,b.name,l.id from(
select sum(code.locked_time-p.bar_accountant_return_time)time,sum(code.image_count)as img_count,tb.id fromgroup by tb.id)l,t_branch b where l.id=b.id(+))d )group by name
为何第一句就报错
select name,max(case tab when 'a' then a.aTime) aTime,
missing right parenthesis
(select 'a' tab,a.c_time as aTime,b.name,a.superior_id as id from(
select sum((p.sende_time-f.county_time)/decode(code.image_count,0,1)) as c_time,tb.superior_id from
t_subordinate_pack p,t_subordinate_batch b,t_flowtrack f,t_user u
,t_branch tb ,t_barcode code where b.pack_id=p.id(+) and b.scan_id=f.barcode(+)
and p.sender_id=u.id(+) and u.branch_id=tb.id(+) and code.barcode=b.scan_id(+)
and f.county_time>=to_date('2009-03-01','yyyy-mm-dd')and tb.level_code<>1
and f.county_time<=to_date('2009-03-31','yyyy-mm-dd')
group by tb.superior_id)a,t_branch b where a.superior_id=b.id)a union all
(select 'b' tab,b.time/b.img_count as bTime,c.name,b.id from(
select sum((p.sende_time-code.create_time))time,sum(code.image_count)as img_count,tb.id from
t_pack p,t_batch b,t_user u,t_branch tb,t_barcode code where
b.pack_id=p.id(+)and code.barcode=b.scan_id(+) and p.sender_id=u.id(+)
and u.branch_id=tb.id(+)and tb.level_code=2
and p.sende_time>=to_date('2009-03-01','yyyy-mm-dd')and b.bar_return_time<=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)b,t_branch c where b.id=c.id(+))b union all
(
select 'c' tab,c.time/img_count as cTime,b.name,c.id from(
select sum(p.sende_time-ba.bar_return_time)time,sum(code.image_count) as img_count,tb.id from
t_dispense_pack p,t_dispense_batch b,t_user u,t_branch tb,t_barcode code,t_batch ba,t_pack pa where
b.pack_id=p.id(+) and code.barcode=b.scan_id(+)and pa.sender_id=u.id(+)
and u.branch_id=tb.id(+)and code.barcode=ba.scan_id(+)and ba.pack_id=pa.id(+)
and p.sende_time>=to_date('2009-03-01','yyyy-mm-dd')
and ba.bar_return_time<=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)c,t_branch b where c.id=b.id(+))c union all
(select 'd'tab,l.time/l.img_count as dTime,b.name,l.id from(
select sum(code.locked_time-p.bar_accountant_return_time)time,sum(code.image_count)as img_count,tb.id from
t_dispense_batch p,t_user u,t_branch tb,t_barcode code,t_batch ba,t_pack pa where
code.barcode=ba.scan_id(+)and pa.sender_id=u.id(+)and code.barcode=p.scan_id(+)
and u.branch_id=tb.id(+)and code.barcode=ba.scan_id(+)and ba.pack_id=pa.id(+)and code.status='6'
and p.bar_accountant_return_time>=to_date('2009-03-01','yyyy-mm-dd')and tb.level_code<>1
and code.locked_time<=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)l,t_branch b where l.id=b.id(+))d )group by name
select name,max(case tab when 'a' then time else null end) atime,
max(case tab when 'b' then time else null end) btime,
max(case tab when 'c' then time else null end) ctime,
max(case tab when 'd' then time else null end) dtime
from (
(select 'a' as tab,a.c_time as Time,b.name as name,a.superior_id as id
from(select sum((p.sende_time-f.county_time)/decode(code.image_count,0,1)) as c_time,tb.superior_id
from t_subordinate_pack p,t_subordinate_batch b,t_flowtrack f,t_user u,t_branch tb ,t_barcode code
where b.pack_id=p.id(+) and b.scan_id=f.barcode(+)
and p.sender_id=u.id(+) and u.branch_id=tb.id(+) and code.barcode=b.scan_id(+)
and f.county_time>=to_date('2009-03-01','yyyy-mm-dd')and tb.level_code <>1
and f.county_time <=to_date('2009-03-31','yyyy-mm-dd')
group by tb.superior_id)a,t_branch b where a.superior_id=b.id)
union all
(select 'b',b.time/b.img_count,c.name,b.id
from(select sum((p.sende_time-code.create_time))time,sum(code.image_count)as img_count,tb.id
from t_pack p,t_batch b,t_user u,t_branch tb,t_barcode code
where b.pack_id=p.id(+)and code.barcode=b.scan_id(+) and p.sender_id=u.id(+)
and u.branch_id=tb.id(+)and tb.level_code=2
and p.sende_time>=to_date('2009-03-01','yyyy-mm-dd')and b.bar_return_time <=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)b,t_branch c where b.id=c.id(+))
union all
(select 'c',c.time/img_count,b.name,c.id
from(select sum(p.sende_time-ba.bar_return_time)time,sum(code.image_count) as img_count,tb.id
from t_dispense_pack p,t_dispense_batch b,t_user u,t_branch tb,t_barcode code,t_batch ba,t_pack pa
where b.pack_id=p.id(+) and code.barcode=b.scan_id(+)and pa.sender_id=u.id(+)
and u.branch_id=tb.id(+)and code.barcode=ba.scan_id(+)and ba.pack_id=pa.id(+)
and p.sende_time>=to_date('2009-03-01','yyyy-mm-dd')
and ba.bar_return_time <=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)c,t_branch b where c.id=b.id(+))
union all
(select 'd',l.time/l.img_count,b.name,l.id
from(select sum(code.locked_time-p.bar_accountant_return_time)time,sum(code.image_count)as img_count,tb.id
from t_dispense_batch p,t_user u,t_branch tb,t_barcode code,t_batch ba,t_pack pa
where code.barcode=ba.scan_id(+)and pa.sender_id=u.id(+)and code.barcode=p.scan_id(+)
and u.branch_id=tb.id(+)and code.barcode=ba.scan_id(+)and ba.pack_id=pa.id(+)and code.status='6'
and p.bar_accountant_return_time>=to_date('2009-03-01','yyyy-mm-dd')and tb.level_code <>1
and code.locked_time <=to_date('2009-03-31','yyyy-mm-dd')
group by tb.id)l,t_branch b where l.id=b.id(+))
)
group by name;