1010101010018 需求量 0 2008-8-6
1010101010018 需求量 12 2008-8-7
1010101010018 需求量 0 2008-8-15
1010101010018 需求量 60 2008-8-19
1010101010018 采购订单 10 2008-8-6
1010101010018 采购订单 10 2008-8-8
1010101010018 采购订单 30 2008-8-17
1010101010018 采购申请 2 2008-8-6
1010101010018 采购申请 50 2008-8-11
1010101010018 采购申请 50 2008-8-20
上表是原始数据,想求一段代码得出如下结果:
1010101010018 需求量 0 2008-8-6
1010101010018 需求量 12 2008-8-13
1010101010018 需求量 60 2008-8-20
1010101010018 采购订单 10 2008-8-6
1010101010018 采购订单 10 2008-8-13
1010101010018 采购订单 30 2008-8-20
1010101010018 采购申请 2 2008-8-6
1010101010018 采购申请 50 2008-8-13
1010101010018 采购申请 50 2008-8-20
请大侠们给予帮助!定感激不进啊!
1010101010018 需求量 12 2008-8-7
1010101010018 需求量 0 2008-8-15
1010101010018 需求量 60 2008-8-19
1010101010018 采购订单 10 2008-8-6
1010101010018 采购订单 10 2008-8-8
1010101010018 采购订单 30 2008-8-17
1010101010018 采购申请 2 2008-8-6
1010101010018 采购申请 50 2008-8-11
1010101010018 采购申请 50 2008-8-20
上表是原始数据,想求一段代码得出如下结果:
1010101010018 需求量 0 2008-8-6
1010101010018 需求量 12 2008-8-13
1010101010018 需求量 60 2008-8-20
1010101010018 采购订单 10 2008-8-6
1010101010018 采购订单 10 2008-8-13
1010101010018 采购订单 30 2008-8-20
1010101010018 采购申请 2 2008-8-6
1010101010018 采购申请 50 2008-8-13
1010101010018 采购申请 50 2008-8-20
请大侠们给予帮助!定感激不进啊!
(a char(13),
b char(10),
c number,
d date
)insert into testT values('1010101010018', '需求量', 0 , to_date('2008-8-6','YYYY-MM-DD'));
insert into testT values('1010101010018', '需求量', 12 , to_date('2008-8-7','YYYY-MM-DD'));
insert into testT values('1010101010018', '需求量', 0 , to_date('2008-8-15','YYYY-MM-DD'));
insert into testT values('1010101010018', '需求量', 60 , to_date('2008-8-19','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购订单', 10 , to_date('2008-8-6','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购订单', 10 , to_date('2008-8-8','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购订单', 30 , to_date('2008-8-17','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购申请', 2 , to_date('2008-8-6','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购申请', 50 , to_date('2008-8-11','YYYY-MM-DD'));
insert into testT values('1010101010018', '采购申请', 50 , to_date('2008-8-20','YYYY-MM-DD'));
SELECT a, b, sum(c),
DECODE (SIGN (7 - (NEXT_DAY (d, '星期三') - d)),
1, TO_CHAR (NEXT_DAY (d, '星期三'), 'YYYY-MM-DD'),
TO_CHAR (d, 'YYYY-MM-DD')
)
FROM testt
GROUP BY a,
b,
DECODE (SIGN (7 - (NEXT_DAY (d, '星期三') - d)),
1, TO_CHAR (NEXT_DAY (d, '星期三'), 'YYYY-MM-DD'),
TO_CHAR (d, 'YYYY-MM-DD')
);--结果:
1010101010018 采购订单 10 2008-8-6
1010101010018 采购订单 10 2008-8-13
1010101010018 采购订单 30 2008-8-20
1010101010018 采购申请 2 2008-8-6
1010101010018 采购申请 50 2008-8-13
1010101010018 采购申请 50 2008-8-20
1010101010018 需求量 0 2008-8-6
1010101010018 需求量 12 2008-8-13
1010101010018 需求量 60 2008-8-20
一个sql好象搞不定..
方法如下:
DROP TABLE test;
create table test
(a char(13),
b char(10),
c number,
d date
);insert into test values('1010101010018', '需求量', 0 , to_date('2008-8-6','YYYY-MM-DD'));
insert into test values('1010101010018', '需求量', 12 , to_date('2008-8-7','YYYY-MM-DD'));
insert into test values('1010101010018', '需求量', 0 , to_date('2008-8-15','YYYY-MM-DD'));
insert into test values('1010101010018', '需求量', 60 , to_date('2008-8-19','YYYY-MM-DD'));
insert into test values('1010101010018', '采购订单', 10 , to_date('2008-8-6','YYYY-MM-DD'));
insert into test values('1010101010018', '采购订单', 10 , to_date('2008-8-8','YYYY-MM-DD'));
insert into test values('1010101010018', '采购订单', 30 , to_date('2008-8-17','YYYY-MM-DD'));
insert into test values('1010101010018', '采购申请', 2 , to_date('2008-8-6','YYYY-MM-DD'));
insert into test values('1010101010018', '采购申请', 50 , to_date('2008-8-11','YYYY-MM-DD'));
insert into test values('1010101010018', '采购申请', 50 , to_date('2008-8-20','YYYY-MM-DD'));
COMMIT;
SELECT A,
B,
SUM(C),
DECODE(MOD(TRUNC(D) - TRUNC(SYSDATE), 7),
0,
D,
TRUNC(SYSDATE) + 7 * (CEIL((TRUNC(D) - TRUNC(SYSDATE)) / 7))) X
FROM TEST
GROUP BY A,
B,
DECODE(MOD(TRUNC(D) - TRUNC(SYSDATE), 7),
0,
D,
TRUNC(SYSDATE) +
7 * (CEIL((TRUNC(D) - TRUNC(SYSDATE)) / 7)))
ORDER BY 1, 2, 4, 3;
输出:
1010101010018 采购订单 10 2008-08-06
1010101010018 采购订单 10 2008-08-13
1010101010018 采购订单 30 2008-08-20
1010101010018 采购申请 2 2008-08-06
1010101010018 采购申请 50 2008-08-13
1010101010018 采购申请 50 2008-08-20
1010101010018 需求量 0 2008-08-06
1010101010018 需求量 12 2008-08-13
1010101010018 需求量 60 2008-08-20