求大侠帮助 本人刚学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 TAB A DEPT QTY
01 1
02 2
03 3
04 4 TAB B
DEPT QTY
01 1
02 2
05 5 我最终想取的结果是
01 1
02 2
03 3
04 4
05 5 可能a表的数据比b表多,也可能b表的数据比a表多
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 TAB A DEPT QTY
01 1
02 2
03 3
04 4 TAB B
DEPT QTY
01 1
02 2
05 5 我最终想取的结果是
01 1
02 2
03 3
04 4
05 5 可能a表的数据比b表多,也可能b表的数据比a表多
union
select * from TAB_B
SELECT M.* FROM TAB_A T WHERE NOT EXISTS (SELECT 1 FROM TAB_B WHERE DEPT = T.DEPT AND QTY = T.QTY)
--2
SELECT M.* FROM TAB_B T WHERE NOT EXISTS (SELECT 1 FROM TAB_A WHERE DEPT = T.DEPT AND QTY = T.QTY)
--3
SELECT M.* FROM TAB_A T WHERE NOT EXISTS (SELECT 1 FROM TAB_B WHERE DEPT = T.DEPT AND QTY = T.QTY)
UNION
SELECT M.* FROM TAB_B T WHERE NOT EXISTS (SELECT 1 FROM TAB_A WHERE DEPT = T.DEPT AND QTY = T.QTY)上述三个,你需要的是哪个?