oracle 复杂判断,单条SQL语句实现复杂逻辑几例~~

最新推荐文章于 2024-01-28 11:17:51 发布

kaiclife

最新推荐文章于 2024-01-28 11:17:51 发布

阅读量295

点赞数

文章标签： oracle 复杂判断

1、按指定规则生成指定商品2002年销售额，其中彩电项值为2001年的销售额加2000年的销售额，微波炉为2000年销售额, 然后汇总彩电+微波炉的2002年总销售额。

表数据如下：

CITY PRODUCT YEAR SALES

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

北京彩电 1999 3000

北京彩电 2000 2500

北京彩电 2001 4500

北京微波炉 1999 800

北京微波炉 2000 7000

北京微波炉 2001 333

北京冰箱 1999 2323

北京冰箱 2000 1212

北京冰箱 2001 7676

天津彩电 1999 212121

天津彩电 2000 434343

天津彩电 2001 564566

天津微波炉 1999 23432

天津微波炉 2000 232

天津微波炉 2001 34234

天津冰箱 1999 324324

天津冰箱 2000 8987686

天津冰箱 2001 768678

要求用SQL实现如下效果：

CITY PRODUCT YEAR SALES

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

天津微波炉 1999 23432

天津微波炉 2000 232

天津微波炉 2001 34234

天津微波炉 2002 232

天津冰箱 1999 324324

天津冰箱 2000 8987686

天津冰箱 2001 768678

天津彩电 1999 212121

天津彩电 2000 434343

天津彩电 2001 564566

天津彩电 2002 998909

天津彩电+微波炉 2002 999141

北京微波炉 1999 800

北京微波炉 2000 7000

北京微波炉 2001 333

北京微波炉 2002 7000

北京冰箱 1999 2323

北京冰箱 2000 1212

北京冰箱 2001 7676

北京彩电 1999 3000

北京彩电 2000 2500

北京彩电 2001 4500

北京彩电 2002 7000

北京彩电+微波炉 2002 14000

建表语句如下：

create table tmp1(CITY varchar2(20), PRODUCT varchar2(20), YEAR number, SALES number);

insert into tmp1 values ('北京','彩电', 1999, 3000);

insert into tmp1 values ('北京','彩电', 2000, 2500);

insert into tmp1 values ('北京','彩电', 2001, 4500);

insert into tmp1 values ('北京','微波炉', 1999, 800);

insert into tmp1 values ('北京','微波炉', 2000, 7000);

insert into tmp1 values ('北京','微波炉', 2001, 333);

insert into tmp1 values ('北京','冰箱', 1999, 2323);

insert into tmp1 values ('北京','冰箱', 2000, 1212);

insert into tmp1 values ('北京','冰箱', 2001, 7676);

insert into tmp1 values ('天津','彩电', 1999, 212121);

insert into tmp1 values ('天津','彩电', 2000, 434343);

insert into tmp1 values ('天津','彩电', 2001, 564566);

insert into tmp1 values ('天津','微波炉', 1999, 23432);

insert into tmp1 values ('天津','微波炉', 2000, 232);

insert into tmp1 values ('天津','微波炉', 2001, 34234);

insert into tmp1 values ('天津','冰箱', 1999, 324324);

insert into tmp1 values ('天津','冰箱', 2000, 8987686);

insert into tmp1 values ('天津','冰箱', 2001, 768678);

解题思路：

本题初看起来一般都会下意识选择通过group by rollup子句生成，但如果你选择直接通过group by rollup的方式：

[php]

JSSWEB> select city, product, year, sum(sales)

2 from tmp1

3 group by city, rollup(product, year)

4 ;

CITY PRODUCT YEAR SUM(SALES)

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

北京冰箱 2000 1212

北京冰箱 1999 2323

北京冰箱 2001 7676

北京冰箱 11211

北京彩电 2000 2500

北京彩电 1999 3000

北京彩电 2001 4500

北京彩电 10000

北京微波炉 2000 7000

北京微波炉 1999 800

北京微波炉 2001 333

北京微波炉 8133

北京 29344

天津冰箱 2000 8987686

天津冰箱 1999 324324

天津冰箱 2001 768678

天津冰箱 10080688

天津彩电 2000 434343

天津彩电 1999 212121

天津彩电 2001 564566

天津彩电 1211030

天津微波炉 2000 232

天津微波炉 1999 23432

天津微波炉 2001 34234

天津微波炉 57898

天津 11349616

26 rows selected

[/php]

这跟预想的结果有不小差距。

这是因为要求的数据是涉及逻辑运算的，比如某些数据需要2001年相加，某些数据由2000+2001汇总，因此我们在group by之前需要先按照条件对sales做些处理，彩电要取2000年和2001年的，而微波炉要取2000年的。

[php]

JSSWEB> select city,

2 product,

3 year,

4 sales,

5 decode(product,

6 '彩电',

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

8 '微波炉',

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

10 from tmp1;

CITY PRODUCT YEAR SALES NS

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

北京彩电 1999 3000 0

北京彩电 2000 2500 2500

北京彩电 2001 4500 4500

北京微波炉 1999 800 0

北京微波炉 2000 7000 7000

北京微波炉 2001 333 0

北京冰箱 1999 2323

北京冰箱 2000 1212

北京冰箱 2001 7676

天津彩电 1999 212121 0

天津彩电 2000 434343 434343

天津彩电 2001 564566 564566

天津微波炉 1999 23432 0

天津微波炉 2000 232 232

天津微波炉 2001 34234 0

天津冰箱 1999 324324

天津冰箱 2000 8987686

天津冰箱 2001 768678

18 rows selected

[/php]

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

[php]

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

[/php]

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

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

[php]

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

[/php]

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

http://download.oracle.com/docs/ ... _10002.htm#i2172805

kaiclife

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle 复杂判断,单条SQL语句实现复杂逻辑几例~~

1、按指定规则生成指定商品2002年销售额，其中彩电项值为2001年的销售额加2000年的销售额，微波炉为2000年销售额, 然后汇总彩电+微波炉的2002年总销售额。表数据如下：CITYPRODUCT YEARSALES-------------------- -----------------北京彩电 1999 3000北京彩电 2000 2500北京彩电 2001 45...
复制链接

扫一扫