to jiezhi(浪子)不好意思漏了门店表了。门店表 md_id(门店) md_name(门店名称) --共有10个门店
to jiezhi(浪子) ( 不好意思。漏了一个门店表门店表md_id (门店编码) md_name(门店名称) 共10个门店
给你做了一个测试:create table t_main( id number, md_id number, move_date date );create table t_detail( id number, sp_id number, move_num number );SQL> select t.* from t_main t; ID MD_ID MOVE_DATE ---------- ---------- ----------- 1 1 2003-07-25 2 1 2003-07-25 3 2 2003-07-25 4 3 2003-07-25SQL> select t.* from t_detail t; ID SP_ID MOVE_NUM ---------- ---------- ---------- 1 10 1 1 11 2 2 10 3 2 12 2 3 13 1 4 11 5 CREATE OR REPLACE PACKAGE PKG_RPT_TEST AS TYPE rctype IS REF CURSOR; PROCEDURE DynamicReport(out_rc OUT rctype);END PKG_RPT_TEST; CREATE OR REPLACE PACKAGE BODY PKG_RPT_TEST AS PROCEDURE DynamicReport(out_rc OUT rctype) IS sqlstr VARCHAR2(32000); CURSOR cur_a IS SELECT DISTINCT MD_ID FROM T_MAIN ORDER BY MD_ID; BEGIN sqlstr :='SELECT A.SP_ID sp_id'; FOR rec IN CUR_A LOOP sqlstr := sqlstr || ',' || 'SUM(DECODE(B.MD_ID,' || rec.MD_ID || ',A.MOVE_NUM)) ' || '门店' || rec.MD_ID || '发货量'; END LOOP; sqlstr := sqlstr || ' FROM T_MAIN B,T_DETAIL A WHERE A.ID=B.ID GROUP BY A.SP_ID'; --返回结果记录集 OPEN out_rc FOR sqlstr; END; END PKG_RPT_TEST; SP_ID 门店1发货量 门店2发货量 门店3发货量 10 4 11 2 5 12 2 13 1 (上面的数字不知道能不能对齐)
select t1.sp_id,t1.cnt "门店1发货量",t2.cnt "门店2发货量",t3.cnt "门店3发货量" from (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 1 group by sp_id,md_id) t1, (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 2 group by sp_id,md_id) t2, (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 3 group by sp_id,md_id) t3where t1.sp_id = t2.sp_id(+) and t1.sp_id = t3.sp_id(+)
select t1.sp_id,t1.cnt "门店1发货量",t2.cnt "门店2发货量",t3.cnt "门店3发货量" from (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 1 group by sp_id,md_id) t1, (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 2 group by sp_id,md_id) t2, (select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt from t_1,t_2 where t_1.id = t_2.id and md_id = 3 group by sp_id,md_id) t3where t1.sp_id = t2.sp_id(+) and t1.sp_id = t3.sp_id(+)
经过一翻大脑细胞活动得出以下sql语句。 望大虾指教。 select b.sp_id, sum(decode(jm_move.md_id,'0001',b.move_num,0))move_0001, sum(decode(jm_move.md_id,'0002',b.move_num,0))move_0002, sum(decode(jm_move.md_id,'0003',b.move_num,0))move_0003, sum(decode(jm_move.md_id,'0003',b.move_num,0))move_0004, .......... sum(decode(jm_move.md_id,'0010',b.move_num,0))move_0010, from main a,detail b, ( select b.sp_id move_id,a.md_id md_id,max(a.move_date) move_date from main a,detail b where a.id=b.id group by b.sp_id,a.md_id) jm_move where a.id=b.id and b.sp_id=jm_move.move_id and a.md_id=jm_move.md_id and a.move_date=jm_move.move_date group by b.sp_id
是否可以使用分析函数,last_value() over(order by rownum),这复杂语句,给人感觉恐慌!
小弟初识oracle。 last_value() over(order by rownum),不是很了解。
md_id(门店) md_name(门店名称)
--共有10个门店
不好意思。漏了一个门店表门店表md_id (门店编码) md_name(门店名称)
共10个门店
id number,
md_id number,
move_date date
);create table t_detail(
id number,
sp_id number,
move_num number
);SQL> select t.* from t_main t; ID MD_ID MOVE_DATE
---------- ---------- -----------
1 1 2003-07-25
2 1 2003-07-25
3 2 2003-07-25
4 3 2003-07-25SQL> select t.* from t_detail t; ID SP_ID MOVE_NUM
---------- ---------- ----------
1 10 1
1 11 2
2 10 3
2 12 2
3 13 1
4 11 5
CREATE OR REPLACE PACKAGE PKG_RPT_TEST AS
TYPE rctype IS REF CURSOR;
PROCEDURE DynamicReport(out_rc OUT rctype);END PKG_RPT_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_RPT_TEST AS
PROCEDURE DynamicReport(out_rc OUT rctype) IS
sqlstr VARCHAR2(32000);
CURSOR cur_a IS SELECT DISTINCT MD_ID
FROM T_MAIN
ORDER BY MD_ID;
BEGIN
sqlstr :='SELECT A.SP_ID sp_id';
FOR rec IN CUR_A LOOP
sqlstr := sqlstr || ',' || 'SUM(DECODE(B.MD_ID,' || rec.MD_ID || ',A.MOVE_NUM)) ' || '门店' || rec.MD_ID || '发货量';
END LOOP;
sqlstr := sqlstr || ' FROM T_MAIN B,T_DETAIL A
WHERE A.ID=B.ID
GROUP BY A.SP_ID';
--返回结果记录集
OPEN out_rc FOR sqlstr;
END;
END PKG_RPT_TEST;
SP_ID 门店1发货量 门店2发货量 门店3发货量
10 4
11 2 5
12 2
13 1 (上面的数字不知道能不能对齐)
from
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 1
group by sp_id,md_id) t1,
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 2
group by sp_id,md_id) t2,
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 3
group by sp_id,md_id) t3where t1.sp_id = t2.sp_id(+)
and t1.sp_id = t3.sp_id(+)
from
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 1
group by sp_id,md_id) t1,
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 2
group by sp_id,md_id) t2,
(select t_1.md_id,t_2.sp_id,sum(t_2.move_num) cnt
from t_1,t_2
where t_1.id = t_2.id and md_id = 3
group by sp_id,md_id) t3where t1.sp_id = t2.sp_id(+)
and t1.sp_id = t3.sp_id(+)
SQL> / SP_ID ??1??? ??2??? ??3???
---------- ---------- ---------- ----------
1 21
20 20 22
30 100SQL>
望大虾指教。
select b.sp_id,
sum(decode(jm_move.md_id,'0001',b.move_num,0))move_0001,
sum(decode(jm_move.md_id,'0002',b.move_num,0))move_0002,
sum(decode(jm_move.md_id,'0003',b.move_num,0))move_0003,
sum(decode(jm_move.md_id,'0003',b.move_num,0))move_0004,
..........
sum(decode(jm_move.md_id,'0010',b.move_num,0))move_0010,
from
main a,detail b,
( select b.sp_id move_id,a.md_id md_id,max(a.move_date) move_date
from main a,detail b
where a.id=b.id
group by b.sp_id,a.md_id) jm_move
where a.id=b.id
and b.sp_id=jm_move.move_id
and a.md_id=jm_move.md_id
and a.move_date=jm_move.move_date
group by b.sp_id
last_value() over(order by rownum),不是很了解。