oracle 行列装置

项目中遇见统计每个课题组的”试剂“与”耗材“采购额与采购次数，当时查询统计“试剂”的采购额与采购次数，然后查询统计"耗材"的采购额与采购次数。这种方式效率很差，反反复复访问数据库很多次，如果oracle提供了行列装置函数就很好的解决此问题。

oracle 11g提供PIOVT

oracle 10g提供的有decode

decode的逻辑如下：

DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

如果value等于if1，那么decode结果就是then1；如果value等于if2，那么结果就是then2；如果value等于if3，那么结果等于then3，如果value匹配不到任意值，就返回false。类是与C#的Switch:

Swtch(value)
{
case "if1":"then1";break;
case "if2":"then2";break;
default :"other";break;
}

 

由于我用的是oracle 10g，现在以decode为例:

select sum(sal), dt.dname
  from EMP t
  join dept dt
    on t.deptno = dt.deptno
 group by dname

显示结果:

 8750   ACCOUNTING
 10875 RESEARCH
 9400   SALES

装置后:

select sum(decode(dt.dname, 'ACCOUNTING', sal)) ACCOUNTING,
       sum(decode(dt.dname, 'RESEARCH', sal)) RESEARCH,
       sum(decode(dt.dname, 'SALES', sal)) SALES
  from EMP t
  join dept dt
    on t.deptno = dt.deptno

显示结果:

ACCOUNTING RESEARCH SALES

   8750 　　　 10875　　　9400

修改关于试剂耗材的统计如下：

SELECT sum(DECODE(producttype, '试剂', od.sumprice)) sjprice,
       count(DECODE(producttype, '试剂', od.sumprice)) sjcount,
       sum(decode(producttype, '耗材', od.sumprice)) hcPrice,
       count(decode(producttype, '耗材', od.sumprice)) hcCount,
       nd.department
  FROM ORDER_INFO o
  join Order_Details od
    on o.orderid = od.orderid
  join user_info u
    on u.id = o.userid
  join neodepart nd
    on nd.id = u.department
 where 1 = 1
 group by nd.department