求一条SQL

显示全部楼层 · 2014-7-4 06:00:11

表 a

id 公司出库数时间
1 A53-8
2 A53-7
3 A53-7
表 b
id 公司入库数时间
1 A5 3-8
1 A10 3-8
要查询结果是
公司入库数出库数时间
A 155
3-8
ANULL
10 3-7

千问 · 2014-7-4 06:00:11

create table aa (id number(2),gs varchar2(2),cks number(2),sj varchar2(10));
insert into aa values(1,'A',5,'3-8');
insert into aa values(2,'A',5,'3-7');
insert into aa values(3,'A',5,'3-7');
create table bb (id number(2),gs varchar2(2),rks number(2),sj varchar2(10));
insert into bb values(1,'A',5,'3-8');
insert into bb values(1,'A',10,'3-8');
select a.gs,b.rks,a.cks,a.sj from
(select aa.gs,aa.sj,sum(aa.cks) cks from aa group by aa.gs,aa.sj) a,
(select bb.gs,bb.sj,sum(bb.rks) rks from bb group by bb.gs,bb.sj) b
where a.sj=b.sj(+)

千问 · 2014-7-4 06:00:11

表错了
出库表
入库表
id 公司是否出库时间 id 公司是否入库时间
1 A 1 3-8
1A 1
3-8
2 A 1 3-7
2 A1
3-8
3 A 1 3-7

要查询结果是
公司入库数出库数时间
A2 1 3-8
A 02 3-7

千问 · 2014-7-4 06:00:11

87589520 发表于 2012-3-8 15:08
表错了
出库表
入库表
id 公司是否出库时间 id 公司是否入库时间 ...
你不就是该了下数据么，你再执行下看看得不到你要的结果？相关字段换一下

千问 · 2014-7-4 06:00:11

SGPM_OUT 发表于 2012-3-8 14:59
create table aa (id number(2),gs varchar2(2),cks number(2),sj varchar2(10));
insert into aa values( ...
大哥还在吗表错了您能重新看看吗

千问 · 2014-7-4 06:00:11

with t1 as (
select 1,'A' company,5 out_amt,'3-8' dt from dual union all
select 2,'A',5,'3-7' from dual union all
select 3,'A',5,'3-7' from dual),
t2 as (
select 1,'A' company,5 in_amt,'3-8' dt from dual union all
select 1,'A',10,'3-8' from dual)
select a.company,b.in_amt,c.out_amt,a.dt from
(select company,dt from t1 union select company,dt from t2) a,
(select company,dt,sum(in_amt) in_amt from t2
group by company,dt) b,
(select company,dt,sum(out_amt) out_amt from t1
group by company,dt) c
where a.company=b.company(+)
and a.dt=b.dt(+)
and a.company=c.company(+)
and a.dt=c.dt(+)

千问 · 2014-7-4 06:00:11

提问请自己写好建表语句，你看大家写那么一堆。
http://www.itpub.net/thread-1348543-1-1.html

千问 · 2014-7-4 06:00:11

87589520 发表于 2012-3-8 15:12
大哥还在吗表错了您能重新看看吗
select aa.gs,sum(bb.是否入库),sum(aa.是否出库),aa.sj fromaa,bb
where aa.sj=bb.sj(+)
group by aa.gs,aa.sj
这个执行起来貌似快点

千问 · 2014-7-4 06:00:11

SQL> select * from a;

ID COMPANY_ID
OUT_NUMBER OPER_DATE
---------- -------------------------------------------------------------------------------- ---------- -----------
1 A
1 2012-3-8
2 A
1 2012-3-7
3 A
1 2012-3-7

SQL> select * from b;

ID COMPANY_ID
IN_NUMBER OPER_DATE
---------- -------------------------------------------------------------------------------- ---------- -----------
1 A
1 2012-3-8
2 A
1 2012-3-8

SQL>
SQL> select distinct company_id,
2
sum(decode(flag,0,out_number,0)) over(partition by oper_date) as "入库数",
3
sum(decode(flag,1,out_number,0)) over(partition by oper_date) as "出库数",
4
oper_date as "时间"
5from (
6
select a.id,a.company_id,a.out_number,a.oper_date, '1' as flag from a
7
union all
8
select b.id,b.company_id,b.in_number,b.oper_date, '0' as flag from b
9) X order by oper_date desc;

COMPANY_ID
入库数出库数时间
-------------------------------------------------------------------------------- ---------- ---------- -----------
A
2
12012-3-8
A
0
22012-3-7

千问 · 2014-7-4 06:00:11

先group by ,再full join