以下用一句SQL语句实现,因考虑到不大可能用一句实现,所以先开20分支票>> > > 一客户销售表,输入数据后排列为:
>> > > id name sale_quantity, sale_date
>> > > 01 張三 100 2003-5-6
>> > > 02 李四 200 2003-5-6
>> > > 01 張三 50 2003-5-6
>> > > 01 張三 25 2003-5-8
>> > > 02 李四 15 2003-5-8
>> > > 01 張三 20 2003-5-10
>> > > .. .
>> > > . .
>> > > 客戶數量不確定,是動態的。
>> > >
>> > > 現在是要將每个客户销售数量汇总后的顯示結果排為
>> > > sale_date 張三 李四 王五 。。
>> > > 2003-5-6 150 200
>> > > 2003-5-8 25 15
>> > > 2003-5-10 20 0
>> > > 此為動態客戶。
>> > > id name sale_quantity, sale_date
>> > > 01 張三 100 2003-5-6
>> > > 02 李四 200 2003-5-6
>> > > 01 張三 50 2003-5-6
>> > > 01 張三 25 2003-5-8
>> > > 02 李四 15 2003-5-8
>> > > 01 張三 20 2003-5-10
>> > > .. .
>> > > . .
>> > > 客戶數量不確定,是動態的。
>> > >
>> > > 現在是要將每个客户销售数量汇总后的顯示結果排為
>> > > sale_date 張三 李四 王五 。。
>> > > 2003-5-6 150 200
>> > > 2003-5-8 25 15
>> > > 2003-5-10 20 0
>> > > 此為動態客戶。
解决方案 »
- pl/sql 如何取前6个月的sql语句。
- oracle utl_http.begin_request错误
- 公司改用DB2,让我情何以堪
- 请教oracle存储过程执行死了,没反应了,谢谢!
- 难得一遇的oracle字符转换number型!!
- 使用Exp如何导出同义词和数据呢?
- 程序运行出错:out of process memroy..... ,我该如何调整参数?
- 新手提问: 怎么查某个SID/SESSION是哪个客户端的? 怎么查回滚段的使用情况,被哪些SESSION使用?
- 如何对plsql中的程序加密,这种加密方法plsql5本身有这功能吗?看别人的加密程序,有时看的到一些代码,但一晃而过
- 两个问题:1、在多个表中如何查询某一条记录;2、如何将文本文件中的数据导入oracle9i
- 有沒有這樣的軟體,可以隱藏正在運行的程式,我可以通這快捷鍵隱藏一個正在運行的程式,又可通過快捷鍵取消隱藏的運行的程式;或有沒有其他的
- 关于Oracle在线用户的问题(在线等)
如果是用dev的report builder的矩阵报表可以实现。
as
cursor t_sor is
select sale_date,sum(decode(name,'張三',sale_quantity,0)) 張三
,sum(decode(name,'李四',sale_quantity,0)) 李四
...
from employee a,sale b where a.id=b.id(+) group by b.sale_date;
begin
for v_sor in t_sor loop
..
end loop;
end;
/
一般要通过代码来自己编写。
先把需要的数据从服务器上读取下来,然后在客户端的程序中通过写代码的方法,将数据转置。
参考用deptno对你的date、ename对你的name、sal对你的qty。
把nvl(sal,0)换成sum(nvl(qty,0))并在那一层的select加group by ..示例:
SQL> select * from sse;JOB DEPTNO SAL
--------- ---------- ----------
CLERK 10 1300
CLERK 20 7900
CLERK 30 950
ANALYST 20 12000
MANAGER 10 2450
MANAGER 20 5975
MANAGER 30 2850
SALESMAN 30 5600
PRESIDENT 10 50009 rows selected. 1 select Rpad(' deptno',8) TYPE,
2 replace(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),'|',' ') list
3 from
4 (select SYS_CONNECT_BY_PATH(lpad(job,8), '|') list, level lev from
5 (select LAG(rownum, 1) OVER (ORDER BY job) c_id,rownum prow,job
6 from (select job from sse group by job) )
7 START WITH c_id is null
8 CONNECT BY PRIOR prow = c_id)
9 union
10 select Rpad(deptno,8) TYPE,
11 replace(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),'|',' ') list
12 from
13 ( select deptno, SYS_CONNECT_BY_PATH(lpad(sal,8), '|') list, level lev from
14 (select LAG(rownum, 1) OVER (PARTITION BY deptno ORDER BY job) c_id,
15 rownum prow,deptno,job,sal from
16 (select * from
17 (select aa.deptno,aa.job,nvl(bb.sal,0) sal
18 from (select deptno,job
19 from (select distinct deptno from sse) a,(select distinct job from sse) b) aa,
20 sse bb
21 where aa.deptno = bb.deptno(+) and aa.job = bb.job(+) )
22 order by deptno,job))
23 START WITH c_id is null
24 CONNECT BY PRIOR prow = c_id)
25* group by deptno
SQL> /TYPE LIST
---------------- ------------------------------------------------------------
deptno ANALYST CLERK MANAGER PRESIDEN SALESMAN
10 0 1300 2450 5000 0
20 12000 7900 5975 0 0
30 0 950 2850 0 5600SQL>
示例:
SQL> select * from sse;JOB DEPTNO SAL
--------- ---------- ----------
CLERK 10 1300
CLERK 20 7900
CLERK 30 950
ANALYST 20 12000
MANAGER 10 2450
MANAGER 20 5975
MANAGER 30 2850
SALESMAN 30 5600
PRESIDENT 10 50009 rows selected.
SQL语句:select deptno,replace(max(list),'|',' ') list from (select deptno,
SYS_CONNECT_BY_PATH(Rpad(decode(job,null,'"',Rpad(job,8)||':'),9)||
lpad(sal,6),'|') list from( select c_id,prow,deptno,
lag(null,1,job) OVER (PARTITION BY job ORDER BY deptno) job,sal from
(select LAG(rownum,1) OVER (PARTITION BY aa.deptno ORDER BY aa.job) c_id,
rownum prow,aa.deptno,aa.job,nvl(bb.sal,0) sal
from (select deptno,job
from (select distinct deptno from sse) a,
(select distinct job from sse) b ) aa, sse bb
where aa.deptno = bb.deptno(+) and aa.job = bb.job(+)
order by aa.deptno,aa.job))
START WITH c_id is null CONNECT BY PRIOR prow = c_id)
group by deptno执行结果:NO LIST
-- ---------------------------------------------------------------------------
10 ANALYST : 0 CLERK : 1300 MANAGER : 2450 PRESIDEN: 5000 SALESMAN: 0
20 " 12000 " 7900 " 5975 " 0 " 0
30 " 0 " 950 " 2850 " 0 " 5600
(id number(8),
name varchar2(20),
sale number(8),
date1 varchar2(20))
insert into b values(01,'aa',100,'2003-5-6');
insert into b values(02,'bb',200,'2003-5-6');
insert into b values(01,'aa',50,'2003-5-6');
insert into b values(01,'aa',25,'2003-5-7');
insert into b values(02,'bb',15,'2003-5-7');
insert into b values(03,'cc',100,'2003-5-7');
insert into b values(01,'aa',300,'2003-5-11');select date1 ,sum(decode(name,'aa',sale)) aa,
sum(decode(name,'bb',sale)) bb,
sum(decode(name,'cc',sale)) cc
from b
group by date1
2* order by deptno,job,sal
SQL> / DEPTNO JOB SAL
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 ANALYST 6000
20 CLERK 3800
20 CLERK 4100
20 MANAGER 5975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1250
30 SALESMAN 1250
30 SALESMAN 1500
30 SALESMAN 160014 rows selected.
SQL语句:
SELECT deptno, REPLACE(MAX(list),'|',' ') list
FROM (SELECT DECODE(deptno,99,'CSUM',TO_CHAR(deptno,999)) deptno,
SYS_CONNECT_BY_PATH(RPAD(DECODE(job,NULL,' ','ZZ','RSUM',job),9)||
LPAD(sal,6),'|') list
FROM (SELECT cid,prow,deptno,sal,
LAG(NULL,1,job) OVER(PARTITION BY job ORDER BY deptno) job
FROM (SELECT LAG(ROWNUM,1) OVER (PARTITION BY deptno ORDER BY job)
cid,ROWNUM prow,deptno,job,sal
FROM (SELECT NVL(d.deptno,99) deptno,NVL(d.job,'ZZ') job,
SUM(NVL(e.sal,0)) sal
FROM (SELECT deptno,job
FROM (SELECT DISTINCT deptno FROM emp) a,
(SELECT DISTINCT job FROM emp) b
) d, emp e
WHERE d.deptno = e.deptno(+) AND d.job = e.job(+)
GROUP BY CUBE(d.deptno,d.job)
)
)
)
START WITH cid IS NULL
CONNECT BY PRIOR prow = cid
)
GROUP BY deptno
/DEPT LIST
---- ------------------------------------------------------------------------
10 ANAL 0 CLER 1300 MANA 2450 PRES 5000 SALE 0 RSUM 8750
20 12000 7900 5975 0 0 25875
30 0 950 2850 0 5600 9400
CSUM 12000 10150 11275 5000 5600 44025