单条SQL语句实现复杂逻辑的几个例子(1)续~

接上篇~~~~

然后在做sum的时候，我们只需要判断一下，比如产品为彩电或微波炉的话，sum(ns)，否则sum(sales)，最后按照城市产品和年份排下序即可：

JSSWEB> select city,

     2         nvl(product, '彩电+微波炉') product,

     3         nvl(year, 2002) year,

     4         decode(product,

     5                '彩电',

     6                nvl2(year, sum(sales), sum(ns)),

     7                '微波炉',

     8                nvl2(year, sum(sales), sum(ns)),

     9                null,

    10                sum(ns),

    11                sum(sales)) sales

    12    from (select city,

    13                 product,

    14                 year,

    15                 sales,

    16                 decode(product,

    17                        '彩电',

    18                        decode(year, 2000, sales, 2001, sales, 0),

    19                        '微波炉',

    20                        decode(year, 2000, sales, 0)) ns

    21            from tmp1) a

    22   group by city, rollup(product, year)

    23  having sum(ns) is not null or year is not null

    24   order by 1, 2, 3;

CITY                 PRODUCT                    YEAR      SALES

-------------------- -------------------- ---------- ----------

北京                 冰箱                       1999       2323

北京                 冰箱                       2000       1212

北京                 冰箱                       2001       7676

北京                 彩电                       1999       3000

北京                 彩电                       2000       2500

北京                 彩电                       2001       4500

北京                 彩电                       2002       7000

北京                 彩电+微波炉                2002      14000

北京                 微波炉                     1999        800

北京                 微波炉                     2000       7000

北京                 微波炉                     2001        333

北京                 微波炉                     2002       7000

天津                 冰箱                       1999     324324

天津                 冰箱                       2000    8987686

天津                 冰箱                       2001     768678

天津                 彩电                       1999     212121

天津                 彩电                       2000     434343

天津                 彩电                       2001     564566

天津                 彩电                       2002     998909

天津                 彩电+微波炉                2002     999141

天津                 微波炉                     1999      23432

天津                 微波炉                     2000        232

天津                 微波炉                     2001      34234

天津                 微波炉                     2002        232

24 rows selected

上述实现从技术角度看并不太难，所使用的函数、语法也都比较常见，只是由于业务需求涉及了一些逻辑，因此在实现的时候不少代码都用在了逻辑判断上，这样就造成了代码过长，同时也降低了可读性。

下面再介绍一种更简单也更高效的实现方式，借助model和partition，可以直接指定计算规则：

JSSWEB> select * from TMP1

     2  MODEL

     3  PARTITION BY (city) DIMENSION BY (product, year)

     4  MEASURES (sales sal)

     5  RULES

     6  (sal['彩电', 2002] = sal['彩电', 2001] + sal['彩电', 2000],

     7  sal['微波炉', 2002] = sal['微波炉', 2000],

     8  sal['彩电+微波炉', 2002] = (sal['彩电',2002]+sal['微波炉',2002]))

     9  ORDER BY 1,2,3

    10  ;

CITY                 PRODUCT                    YEAR        SAL

-------------------- -------------------- ---------- ----------

北京                 冰箱                       1999       2323

北京                 冰箱                       2000       1212

北京                 冰箱                       2001       7676

北京                 彩电                       1999       3000

北京                 彩电                       2000       2500

北京                 彩电                       2001       4500

北京                 彩电                       2002       7000

北京                 彩电+微波炉                2002      14000

北京                 微波炉                     1999        800

北京                 微波炉                     2000       7000

北京                 微波炉                     2001        333

北京                 微波炉                     2002       7000

天津                 冰箱                       1999     324324

天津                 冰箱                       2000    8987686

天津                 冰箱                       2001     768678

天津                 彩电                       1999     212121

天津                 彩电                       2000     434343

天津                 彩电                       2001     564566

天津                 彩电                       2002     998909

天津                 彩电+微波炉                2002     999141

天津                 微波炉                     1999      23432

天津                 微波炉                     2000        232

天津                 微波炉                     2001      34234

天津                 微波炉                     2002        232

24 rows selected

*更多关于model子句的语法可以参考：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2172805