本人用的是oracle 8i
数据库里面有5张表
退货表
退货日期 单位代码 药品代码 退货金额 退货数量 办事处代码
RQ DWDM YPDM YPM THJE THSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19940725 20008 1051 酞丁安滴眼液 500 2000 104
19940725 44010 1051 酞丁安滴眼液 5500 10000 111
19940525 44801 1041 氢化醋酸可的松滴眼液 3660 6000 111
19940825 44056 1071 利福平滴眼液 400 1000 111
19940825 44056 2071 克霉唑癣药水 280 1000 111
19940825 44056 2031 牙痛水 180 1000 111
19940825 44056 1051 酞丁安滴眼液 550 1000 111
19940925 24005 1051 酞丁安滴眼液 -250 -1000 102
19940525 36028 1191 淡鼻眼净 1300 10000 110
19930725 42528 4011 醋酸强的松片 380 200 111
19931225 36033 1051 酞丁安滴眼液 567 2268 110
19940625 11010 1071 利福平滴眼液 3870 9000 109
19940324 25001 2071 克霉唑癣药水 2800 10000 105
19940429 24005 1051 酞丁安滴眼液 500 1000 102
19940325 24005 1051 酞丁安滴眼液 250 1000 102
发货表 RQ DWDM YPDM YPM FHJE FHSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19931204 25017 1181 浓鼻眼净 260 2000 105
19931204 25017 1001 无环鸟苷滴眼液 435 1000 105
19931204 25017 1201 氯霉素滴眼液 1100 10000 105
19931204 25017 1221 病毒灵滴眼液 153 1000 105
19921119 25001 2071 克霉唑癣药水 2800 10000 105
19910418 42506 4101 甲硝唑片(瓶装) 7680 3200 111
19910418 42506 4081 CO新诺明片(瓶装) 4020 600 111
19901220 42506 1181 浓鼻眼净 2500 20000 111
19901220 42506 1201 氯霉素滴眼液 2100 20000 111
19901220 42506 1031 醋酸可的松滴眼液 5000 20000 111
19901220 42506 4011 醋酸强的松片 600 400 111
19901220 42506 4081 CO新诺明片(瓶装) 11520 1600 111回款表 RQ DWDM YPDM YPM HKJE HKSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19940621 37017 1181 浓鼻眼净 1300 10000 108
19940621 37017 2081 滴耳油 440 2000 108
19940621 37017 1241 消炎滴眼液 800 5000 108
19940621 37017 1322 白可明滴眼液(老包装) 1056 2400 108
19940621 37017 1021 病毒唑滴眼液 1250 5000 108
19940621 37017 1181 浓鼻眼净 1300 10000 108
19940621 37017 1241 消炎滴眼液 480 3000 108
19940621 37017 1322 白可明滴眼液(老包装) 1056 2400 108
19940624 15004 1141 的确当滴眼液 9500 10000 106
19940625 44508 1231 斑马滴眼液 700 5000 111
19940625 44508 1011 地塞米松磷酸钠滴眼液 1250 5000 111
19940625 44508 1001 无环鸟苷滴眼液 2100 5000 111
19940625 44508 1051 酞丁安滴眼液 2750 5000 111药品库表 YPDM YPQC
--------- -------------------
1221 盐酸吗啉呱滴眼液
1231 磺胺醋酰钠滴眼液
1241 消炎滴眼液
1251 硫酸卡那霉素滴眼液
1261 复方硫酸锌滴眼液
2011 盐酸林可霉素滴耳液
2021 CO新霉素滴耳液
1191 盐酸萘甲唑啉滴鼻液
2041 风油精
2051 开塞露(小)
2061 开塞露(大)
2071 克霉唑溶液
2081 硼酸冰片滴耳液
3011 迪乐冲剂
3021 复方磺胺甲恶唑颗粒
3031 小儿速效感冒冲剂要求 对药品库的产品按照日期和单位代码进行汇总查询,并将退货表,回款表,发货表按照产品进行对应连接,本人税票有限只能完成单表操作 select sum(thje),sum(thsl),yp.ypdm from yp,th where th.ypdm = yp.ypdm and rq >19930101 and dwdm = 37017 group by yp.ypdm,谢谢大家的帮忙
数据库里面有5张表
退货表
退货日期 单位代码 药品代码 退货金额 退货数量 办事处代码
RQ DWDM YPDM YPM THJE THSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19940725 20008 1051 酞丁安滴眼液 500 2000 104
19940725 44010 1051 酞丁安滴眼液 5500 10000 111
19940525 44801 1041 氢化醋酸可的松滴眼液 3660 6000 111
19940825 44056 1071 利福平滴眼液 400 1000 111
19940825 44056 2071 克霉唑癣药水 280 1000 111
19940825 44056 2031 牙痛水 180 1000 111
19940825 44056 1051 酞丁安滴眼液 550 1000 111
19940925 24005 1051 酞丁安滴眼液 -250 -1000 102
19940525 36028 1191 淡鼻眼净 1300 10000 110
19930725 42528 4011 醋酸强的松片 380 200 111
19931225 36033 1051 酞丁安滴眼液 567 2268 110
19940625 11010 1071 利福平滴眼液 3870 9000 109
19940324 25001 2071 克霉唑癣药水 2800 10000 105
19940429 24005 1051 酞丁安滴眼液 500 1000 102
19940325 24005 1051 酞丁安滴眼液 250 1000 102
发货表 RQ DWDM YPDM YPM FHJE FHSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19931204 25017 1181 浓鼻眼净 260 2000 105
19931204 25017 1001 无环鸟苷滴眼液 435 1000 105
19931204 25017 1201 氯霉素滴眼液 1100 10000 105
19931204 25017 1221 病毒灵滴眼液 153 1000 105
19921119 25001 2071 克霉唑癣药水 2800 10000 105
19910418 42506 4101 甲硝唑片(瓶装) 7680 3200 111
19910418 42506 4081 CO新诺明片(瓶装) 4020 600 111
19901220 42506 1181 浓鼻眼净 2500 20000 111
19901220 42506 1201 氯霉素滴眼液 2100 20000 111
19901220 42506 1031 醋酸可的松滴眼液 5000 20000 111
19901220 42506 4011 醋酸强的松片 600 400 111
19901220 42506 4081 CO新诺明片(瓶装) 11520 1600 111回款表 RQ DWDM YPDM YPM HKJE HKSL SALER
--------- --------- --------- -------------------- --------- --------- ---
19940621 37017 1181 浓鼻眼净 1300 10000 108
19940621 37017 2081 滴耳油 440 2000 108
19940621 37017 1241 消炎滴眼液 800 5000 108
19940621 37017 1322 白可明滴眼液(老包装) 1056 2400 108
19940621 37017 1021 病毒唑滴眼液 1250 5000 108
19940621 37017 1181 浓鼻眼净 1300 10000 108
19940621 37017 1241 消炎滴眼液 480 3000 108
19940621 37017 1322 白可明滴眼液(老包装) 1056 2400 108
19940624 15004 1141 的确当滴眼液 9500 10000 106
19940625 44508 1231 斑马滴眼液 700 5000 111
19940625 44508 1011 地塞米松磷酸钠滴眼液 1250 5000 111
19940625 44508 1001 无环鸟苷滴眼液 2100 5000 111
19940625 44508 1051 酞丁安滴眼液 2750 5000 111药品库表 YPDM YPQC
--------- -------------------
1221 盐酸吗啉呱滴眼液
1231 磺胺醋酰钠滴眼液
1241 消炎滴眼液
1251 硫酸卡那霉素滴眼液
1261 复方硫酸锌滴眼液
2011 盐酸林可霉素滴耳液
2021 CO新霉素滴耳液
1191 盐酸萘甲唑啉滴鼻液
2041 风油精
2051 开塞露(小)
2061 开塞露(大)
2071 克霉唑溶液
2081 硼酸冰片滴耳液
3011 迪乐冲剂
3021 复方磺胺甲恶唑颗粒
3031 小儿速效感冒冲剂要求 对药品库的产品按照日期和单位代码进行汇总查询,并将退货表,回款表,发货表按照产品进行对应连接,本人税票有限只能完成单表操作 select sum(thje),sum(thsl),yp.ypdm from yp,th where th.ypdm = yp.ypdm and rq >19930101 and dwdm = 37017 group by yp.ypdm,谢谢大家的帮忙
SELECT TH.RQ, YP.YPQC, YP.YPDM, SUM(THJE), SUM(THSL)
FROM YP, TH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT TH.RQ, YP.YPQC, YP.YPDM, SUM(FHJE), SUM(FHSL)
FROM YP, FH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT TH.RQ, YP.YPQC, YP.YPDM, SUM(HKJE), SUM(HKSL)
FROM YP, HK
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;
修改引用错误.
SELECT RQ, YP.YPQC, YP.YPDM, SUM(THJE), SUM(THSL)
FROM YP, TH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT RQ, YP.YPQC, YP.YPDM, SUM(FHJE), SUM(FHSL)
FROM YP, FH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT RQ, YP.YPQC, YP.YPDM, SUM(HKJE), SUM(HKSL)
FROM YP, HK
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;
再修改引用错误。
SELECT RQ, YP.YPQC, YP.YPDM, SUM(THJE), SUM(THSL)
FROM YP, TH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT RQ, YP.YPQC, YP.YPDM, SUM(FHJE), SUM(FHSL)
FROM YP, FH
WHERE FH.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;SELECT RQ, YP.YPQC, YP.YPDM, SUM(HKJE), SUM(HKSL)
FROM YP, HK
WHERE HK.YPDM = YP.YPDM
GROUP BY YP.YPDM, RQ
ORDER BY RQ;
你那是一个个的查询最好我还要把这3个查询按照药品表汇总成一个表,这个怎么做
SELECT YPQC, YPDM, SUM(A), SUM(B)
FROM (SELECT YP.YPQC, YP.YPDM, SUM(THJE) A, SUM(THSL) B
FROM YP, TH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM
UNION
SELECT YP.YPQC, YP.YPDM, SUM(FHJE) A, SUM(FHSL) B
FROM YP, FH
WHERE FH.YPDM = YP.YPDM
UNION
SELECT YP.YPQC, YP.YPDM, SUM(HKJE) A, SUM(HKSL) B
FROM YP, HK
WHERE HK.YPDM = YP.YPDM)
GROUP BY YPQC, YPDM;
SELECT YPQC, YPDM, a, b
FROM (SELECT YP.YPQC, YP.YPDM, SUM(THJE) a, SUM(THSL) b
FROM YP, TH
WHERE TH.YPDM = YP.YPDM
GROUP BY YP.YPDM,yp.ypqc
UNION
SELECT YP.YPQC, YP.YPDM, SUM(FHJE) a, SUM(FHSL) b
FROM YP, FH
WHERE FH.YPDM = YP.YPDM
GROUP BY YP.YPDM,yp.ypqc
UNION
SELECT YP.YPQC, YP.YPDM, SUM(HKJE) a, SUM(HKSL) b
FROM YP, HK
WHERE HK.YPDM = YP.YPDM
GROUP BY YP.YPDM,yp.ypqc)
GROUP BY YPQC, YPDM;
不过与有个问题是你把退货表,发货表,回款表的汇总字段都放到a,b上去了,我想分开应该怎么做
nvl(b.thje,0), nvl(b.thsl,0),
nvl(c.fhje,0), nvl(c.fhsl,0),
nvl(d.hkje,0), nvl(d.hksl,0)
from
yp a,
(select ypdm, sum(thje) as thje, sum(thsl) as thsl
from th
where rq >19930101
and dwdm = 37017
group by ypdm) b,
(select ypdm, sum(fhje) as fhje, sum(fhsl) as fhsl
from fh
where rq >19930101
and dwdm = 37017
group by ypdm) c,
(select ypdm, sum(hkje) as hkje, sum(hksl) as hksl
from hk
where rq >19930101
and dwdm = 37017
group by ypdm) d
where a.ypdm=b.ypdm(+)
and a.ypdm=c.ypdm(+)
and a.ypdm=d.ypdm(+)
/