求大侠帮助 本人刚学sql没多久,现在碰到一个难题 ,如下sql, 怎样取ab表和cd表相等的数据和不相等的数据?
select ab.*
from
(SELECT '00000110' as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(c.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM chgown c,
stkoutprepare sp,
stkoutprepared spd,
dept d,
waredict w,
goodsclass g
WHERE c.outpreno = sp.outpreno
AND sp.outpreno = spd.outpreno
AND d.dept = c.insubcono
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
AND sp.accflag = '2'
AND NVL(d.attr1, '2') = '2'
And sp.sourcetype = '29'
And spd.attr12 = '1'
And sp.dateaccount = '20080127'
group by '00000110',
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(c.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM chgown c,
stkoutprepare sp,
stkoutprepared spd,
dept d,
waredict w,
goodsclass g
WHERE c.outpreno = sp.outpreno
AND sp.outpreno = spd.outpreno
AND d.dept = c.insubcono
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
AND sp.accflag = '2'
AND NVL(d.attr1, '2') = '1'
And sp.sourcetype = '01'
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM stkoutprepare sp,
stkoutprepared spd,
clients c
WHERE sp.outpreno = spd.outpreno
AND sp.accflag = '2'
And sp.sourcetype = '08'
And sp.subcono = '0100'
and c.cstid = sp.cstid
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype
union
SELECT '00000110' as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.inpreno) AS count,
SUM((-1) * NVL(spd.VALUE, 0)) AS value,
SUM((-1) * NVL(spd.tax, 0)) AS tax,
SUM((-1) * NVL(spd.costvalue, 0)) AS costvalue
FROM stkinprepare sp,
stkinprepared spd,
waredict w,
goodsclass g,
dept d
WHERE sp.inpreno = spd.inpreno
And sp.subcono = '0100'
and sp.cstdept = d.dept
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
And sp.sourcetype = '56'
and nvl(d.attr1, '2') = '2'
And sp.intype = '6'
And spd.attr12 = '1'
And sp.dateaccount = '20080127'
GROUP BY '00000110',
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM (select distinct c.attr10 as attr10
from checkbill c,
stkoutprepare s
where c.uptype = '5'
and s.outpreno = c.attr10) cp,
stkoutprepare sp,
stkoutprepared spd,
clients c
WHERE cp.attr10 = sp.outpreno
AND sp.outpreno = spd.outpreno
AND sp.accflag = '2'
And sp.sourcetype = '01'
And sp.subcono = '0100'
and c.cstid = sp.cstid
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype) ab, (select s.sourcebill,
c.cstcode,
c.dept,
c.dname,
s.surtype,
sum(sd.value) as value
from clients c,
salinvoiced sd,
salinvoice s
where s.makedate >= to_date('20080110', 'yyyymmdd')
and s.dateaccount = '20080127'
and s.cstid = c.cstid
and sd.salino = s.salino
and c.cstcode <> '010001'
group by s.sourcebill,
c.cstcode,
c.dept,
c.dname,
s.surtype) cd
select ab.*
from
(SELECT '00000110' as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(c.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM chgown c,
stkoutprepare sp,
stkoutprepared spd,
dept d,
waredict w,
goodsclass g
WHERE c.outpreno = sp.outpreno
AND sp.outpreno = spd.outpreno
AND d.dept = c.insubcono
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
AND sp.accflag = '2'
AND NVL(d.attr1, '2') = '2'
And sp.sourcetype = '29'
And spd.attr12 = '1'
And sp.dateaccount = '20080127'
group by '00000110',
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(c.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM chgown c,
stkoutprepare sp,
stkoutprepared spd,
dept d,
waredict w,
goodsclass g
WHERE c.outpreno = sp.outpreno
AND sp.outpreno = spd.outpreno
AND d.dept = c.insubcono
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
AND sp.accflag = '2'
AND NVL(d.attr1, '2') = '1'
And sp.sourcetype = '01'
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM stkoutprepare sp,
stkoutprepared spd,
clients c
WHERE sp.outpreno = spd.outpreno
AND sp.accflag = '2'
And sp.sourcetype = '08'
And sp.subcono = '0100'
and c.cstid = sp.cstid
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype
union
SELECT '00000110' as cstid,
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.inpreno) AS count,
SUM((-1) * NVL(spd.VALUE, 0)) AS value,
SUM((-1) * NVL(spd.tax, 0)) AS tax,
SUM((-1) * NVL(spd.costvalue, 0)) AS costvalue
FROM stkinprepare sp,
stkinprepared spd,
waredict w,
goodsclass g,
dept d
WHERE sp.inpreno = spd.inpreno
And sp.subcono = '0100'
and sp.cstdept = d.dept
AND w.goodid = spd.goodid
AND g.classcode = w.classcode
And sp.sourcetype = '56'
and nvl(d.attr1, '2') = '2'
And sp.intype = '6'
And spd.attr12 = '1'
And sp.dateaccount = '20080127'
GROUP BY '00000110',
d.dept,
d.deptname,
sp.sourcecode,
sp.sourcetype
union
SELECT sp.cstid as cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype,
COUNT(sp.outpreno) AS count,
SUM(NVL(spd.VALUE, 0)) AS value,
SUM(NVL(spd.tax, 0)) AS tax,
SUM(NVL(spd.costvalue, 0)) AS costvalue
FROM (select distinct c.attr10 as attr10
from checkbill c,
stkoutprepare s
where c.uptype = '5'
and s.outpreno = c.attr10) cp,
stkoutprepare sp,
stkoutprepared spd,
clients c
WHERE cp.attr10 = sp.outpreno
AND sp.outpreno = spd.outpreno
AND sp.accflag = '2'
And sp.sourcetype = '01'
And sp.subcono = '0100'
and c.cstid = sp.cstid
And sp.dateaccount = '20080127'
GROUP BY sp.cstid,
c.cstcode,
c.dname,
sp.sourcecode,
sp.sourcetype) ab, (select s.sourcebill,
c.cstcode,
c.dept,
c.dname,
s.surtype,
sum(sd.value) as value
from clients c,
salinvoiced sd,
salinvoice s
where s.makedate >= to_date('20080110', 'yyyymmdd')
and s.dateaccount = '20080127'
and s.cstid = c.cstid
and sd.salino = s.salino
and c.cstcode <> '010001'
group by s.sourcebill,
c.cstcode,
c.dept,
c.dname,
s.surtype) cd
解决方案 »
- imp/exp请教
- 数据库父子关系的疑问?
- 有谁用过loadjava工具,请教!
- 急,请教关于返回Oracle表主关键字问题
- 为何只提示 “警告: 创建的过程带有编译错误。”,不指出错在哪行。
- 请问oracle 9i AS portal 能做论坛吗?
- oracle 8 不支持join吗?
- alter system quiesce restricted具体的例子谁有吗?能举个例子吗?
- Select * From PayInvoice Where Uptodate =2002-12-31 为什么没有返回值
- Oracle数据库分离数据
- 修改oracle参数中的pga_aggregate_target是否有风险?
- 外资银行招聘数据库管理员-待优 -shanghai
例:表a,b中相同信息
(select * from a)
intersect
(select * from b)2. minus 返回只在一个表中表现的信息
例: 表a 中有,表b中没有的信息
(select * from a)
minus
(select * from b)
01 1
02 2
03 3
04 4TAB B
DEPT QTY
01 1
02 2
05 5我最终想取的结果是
01 1
02 2
03 3
04 4
05 5可能a表的数据比b表大
select decode(a.dept,null,b.dept,a.dept) dept,
decode(a.qty,null,b.qty,a.qty) qty
from a,b where a.dept = b.dept(+)
union
select decode(a.dept,null,b.dept,a.dept) dept,
decode(a.qty,null,b.qty,a.qty) qty
from a,b where a.dept(+) = b.dept第二种
select decode(a.dept,null,b.dept,a.dept) dept,
decode(a.qty,null,b.qty,a.qty) qty
from a full join b on a.dept=b.dept
union
(select * from taba
minus
select * from tabb);
minus 是不同的