Table_A结构如下:
GOODS_CD DATE QTY AMT Invoice A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002Table_B的结构为
Invoice TotalAmt
INV001 20000
INV002 40000我想得到从20111001~20111031之间的明显记录以及按照INVOICE统计金额,以及Table_B中的金额。
如下:
A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
INV001 500000 20000
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002
INV002 400000 40000如何得到格式如上的结果。
GOODS_CD DATE QTY AMT Invoice A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002Table_B的结构为
Invoice TotalAmt
INV001 20000
INV002 40000我想得到从20111001~20111031之间的明显记录以及按照INVOICE统计金额,以及Table_B中的金额。
如下:
A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
INV001 500000 20000
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002
INV002 400000 40000如何得到格式如上的结果。
2 SELECT 'A' goods_cd,to_date('20111001','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
3 SELECT 'B' goods_cd,to_date('20111011','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
4 SELECT 'D' goods_cd,to_date('20111005','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
5 SELECT 'A' goods_cd,to_date('20111021','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
6 SELECT 'C' goods_cd,to_date('20111031','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
7 SELECT 'B' goods_cd,to_date('20111002','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
8 SELECT 'E' goods_cd,to_date('20111008','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
9 SELECT 'F' goods_cd,to_date('20111013','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
10 SELECT 'G' goods_cd,to_date('20111026','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual
11 )
12 ,t2 AS (
13 SELECT 'INV001' invoice,20000 totalamt FROM dual UNION ALL
14 SELECT 'INV002' invoice,40000 totalamt FROM dual
15 )
16 SELECT t1.goods_cd,
17 t1.tdate,
18 decode(GROUPING(t1.goods_cd), 1, NULL, SUM(t1.qty)) qty,
19 SUM(t1.amt) amt,
20 t1.invoice,
21 decode(GROUPING(t1.goods_cd), 1, MAX(t2.totalamt), NULL) totalamt
22 FROM t1
23 LEFT JOIN t2 ON t1.invoice = t2.invoice
24 GROUP BY GROUPING SETS(t1.invoice,(t1.goods_cd, t1.tdate, t1.invoice, t2.invoice))
25 ;GOODS_CD TDATE QTY AMT INVOICE TOTALAMT
-------- ----------- ---------------------------------------- ---------- ------- ----------
A 2011-10-1 1000 10000 INV001
A 2011-10-21 1000 10000 INV001
B 2011-10-11 1000 10000 INV001
C 2011-10-31 1000 10000 INV001
D 2011-10-5 1000 10000 INV001
50000 INV001 20000
B 2011-10-2 1000 10000 INV002
E 2011-10-8 1000 10000 INV002
F 2011-10-13 1000 10000 INV002
G 2011-10-26 1000 10000 INV002
40000 INV002 40000
需要改下:
SELECT t1.goods_cd,
t1.tdate,
decode(GROUPING(t1.goods_cd), 1, NULL, SUM(t1.qty)) qty,
SUM(t1.amt) amt,
t1.invoice,GROUPING(t1.goods_cd)
FROM t1
GROUP BY GROUPING SETS(t1.invoice,(t1.goods_cd, t1.tdate, t1.invoice,ROWNUM))
t1.tdate,
decode(GROUPING(t1.goods_cd), 1, NULL, SUM(t1.qty)) qty,
SUM(t1.amt) amt,
t1.invoice,
decode(GROUPING(t1.goods_cd), 1, MAX(t2.totalamt), NULL) totalamt
FROM t1
LEFT JOIN t2 ON t1.invoice = t2.invoice
GROUP BY GROUPING SETS(t1.invoice,(t1.goods_cd, t1.tdate, t1.invoice, t2.invoice,ROWNUM))
如下:
A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
INV001 500000
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002
INV002 400000 语句怎样写?
SELECT 'A' goods_cd,to_date('20101001','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'A' goods_cd,to_date('20101001','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'A' goods_cd,to_date('20101001','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'A' goods_cd,to_date('20111001','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'B' goods_cd,to_date('20111011','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'D' goods_cd,to_date('20111005','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'A' goods_cd,to_date('20111021','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'C' goods_cd,to_date('20111031','yyyymmdd') tdate,1000 qty,10000 amt,'INV001' invoice FROM dual UNION ALL
SELECT 'B' goods_cd,to_date('20111002','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
SELECT 'E' goods_cd,to_date('20111008','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
SELECT 'F' goods_cd,to_date('20111013','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual UNION ALL
SELECT 'G' goods_cd,to_date('20111026','yyyymmdd') tdate,1000 qty,10000 amt,'INV002' invoice FROM dual
)
,t2 AS (
SELECT 'INV001' invoice,20000 totalamt FROM dual UNION ALL
SELECT 'INV002' invoice,40000 totalamt FROM dual
)
SELECT t1.goods_cd,
t1.tdate,
decode(GROUPING(t1.goods_cd), 1, NULL, SUM(t1.qty)) qty,
SUM(t1.amt) amt,
t1.invoice,
decode(GROUPING(t1.goods_cd), 1, MAX(t2.totalamt), NULL) totalamt
FROM t1
LEFT JOIN t2 ON t1.invoice = t2.invoice
--这里加一下时间条件
where tdate between to_date('2011-10-01','yyyy-mm-dd') and to_date('2011-10-31','yyyy-mm-dd')
GROUP BY GROUPING SETS(t1.invoice,(t1.goods_cd, t1.tdate, t1.invoice, t2.invoice))
Goods_cd/Date/QTY/AMT/INVOICE
A 20111001 1000 10000 INV001
B 20111011 1000 10000 INV001
D 20111005 1000 10000 INV001
A 20111021 1000 10000 INV001
C 20111031 1000 10000 INV001
INV001 5000 50000 20000
B 20111002 1000 10000 INV002
E 20111008 1000 10000 INV002
F 20111013 1000 10000 INV002
G 20111026 1000 10000 INV002
INV002 4000 40000