---
title: "交叉报表实战：皮鞋规格颜色统计"
author: "java1573"
date: "2009-05-10T22:00:22Z"
tags: []
slug: "untitled"
draft: false
lastmod: "2009-05-10T22:00:22Z"
id: 383297
url: "https://java1573.iteye.com/blog/383297"
---

**知识点：decode,sum，计算列，存储过程**

**业务描述：**  
**产品表**      
**产品编号 尺寸(码)  颜色  数量**  
**1  38   黑  100**  
**1  38   红  10**  
**1  40   黑  50**  
**2  38   黑  100**  
**3  38   红  5**

产品名称表    
产品编号 产品名称  
1  红蜘蛛皮鞋  
2  蓝蜘蛛布鞋  
3  黑寡妇皮鞋

要求展示报表如下：  
统计报表    
尺寸/颜色 黑  红  
38  200 15  
40  50  0

建表：  
create table T\_SHOES  
(  
  P\_ID    NUMBER(10),  
  P\_SIZE  NUMBER(3),  
  P\_COLOR VARCHAR2(6),  
  P\_NUM   NUMBER(5)  
);  
输入测试数据如下：  
select p\_id, p\_size, p\_color, p\_num from t\_shoes;    
P\_ID P\_SIZE P\_COLOR P\_NUM  
1 38 黑 100  
1 38 红 10  
1 40 黑 50  
2 38 黑 100  
3 38 红 5  

因为结果集要通过存储过程,所以创建结果统计报表：  
create table t\_shoes\_report  
(  
  P\_SIZE  NUMBER(3),  
  P\_COLOR VARCHAR2(6),  
  P\_NUM   NUMBER(5)  
);

创建视图,以提供所有颜色和尺寸的匹配情况(利用了笛卡尔乘积)    
create or replace view v\_all\_products as  
select distinct all\_size.p\_size,all\_color.p\_color  
from  
(select distinct t.p\_size from t\_shoes t ) all\_size,  
(select distinct t.p\_color from t\_shoes t) all\_color

通过逐步打印,完成以下存储过程的编写：  
create or replace procedure  sp\_report  
  as  
  str1 number(3);  
  str2 varchar2(30000);  
begin  
   delete from t\_shoes\_report;  
   for all\_size in (select distinct p\_size from v\_all\_products )  
   loop    
      --dbms\_output.put\_line(all\_size.p\_size);    
      for all\_color in ( select distinct p\_color from v\_all\_products where p\_size = all\_size.p\_size)    
      loop    
         --dbms\_output.put\_line(all\_size.p\_size||','||all\_color.p\_color);    
         --统计    
        select nvl(sum(t.p\_num),0) into str1 from t\_shoes t    
         where t.p\_size = all\_size.p\_size    
         and t.p\_color  = all\_color.p\_color;    
         --str2 := all\_size.p\_size||','||all\_color.p\_color||','||str1;    
         --dbms\_output.put\_line(str2);    
         insert into t\_shoes\_report(p\_size,p\_color,p\_num)    
         values(all\_size.p\_size,all\_color.p\_color,str1);    
      end loop;    
   end loop;      
   commit;        
end ;    

测试：      
SQL&gt; execute sp\_report;    

PL/SQL procedure successfully completed      
SQL&gt; select * from t\_shoes\_report;      
P\_SIZE P\_COLOR  P\_NUM    表A  
\------ ------- ------      
    38 黑         200      
    38 红          15      
    40 黑          50      
    40 红           0    

在得到上面的数据,可以通过以下方法进行报表显示      
报表      
尺寸/颜色 黑 红  
38 200 15  
40 50 0

1、通过java程序将表A数据转化HTML代码,显示为表B      
2、通过SQL语句转化      
select      
   t.p\_size 尺寸,    
   sum(decode(t.p\_color,'黑',t.p\_num)) 黑,    
   sum(decode(t.p\_color,'红',t.p\_num)) 红      
from t\_shoes\_report t    
group by t.p\_size    
      
结果：      
尺寸 黑 红  
38 200 15  
40 50 0