一个题目，求思路

下面是一张表：

price              product
--------------------------------------
10                 A
20                 B
30                 B
40                 C
50                 C
60                 C

A、B、C三中产品都只选一个结果会有以下情况：

1.    10 + 20 + 40 = 70
2.    10 + 20 + 50 = 80
3.    10 + 20 + 60 = 90
4.    10 + 30 + 40 = 80
5.    10 + 30 + 50 = 90
6.    10 + 30 + 60 = 100

用PLSQL能把这几种方案给算出来就OK了

 

for cur_1 in (select price from youtable where product = 'A') loop
    for cur_2 in (select price from youtable where product = 'B') loop
        for cur_3 in (select price from youtable where product = 'C') loop

        end loop;
    end loop;
end loop;

SELECT Aprice||'+'||Bprice|| '+'||Cprice|| '='|| to_char(Aprice+Bprice+Cprice) result FROM 
(select price AS Aprice  from testTable where product='A') ,
(SELECT PRICE Bprice FROM   testTable where product='B') ,
(SELECT PRICE Cprice FROM testTable where product='C') 
order by Aprice,Bprice,Cprice

[quote="wqmgxj"]

[code="java"]

SELECT Aprice||'+'||Bprice|| '+'||Cprice|| '='|| to_char(Aprice+Bprice+Cprice) result FROM

(select price AS Aprice from testTable where product='A') ,

(SELECT PRICE Bprice FROM testTable where product='B') ,

(SELECT PRICE Cprice FROM testTable where product='C')

order by Aprice,Bprice,Cprice

[/code]

[/quote]

 

 

 

 

升级一下：如果其中产品组是可选的呢？

如下：

 

 

price              product        condition(是否可选)
-----------------------------------------------
10                 A Y
20                 B N
30                 B N
40                 C Y
50                 C Y
60                 C                       Y

 

 

A、B、C三中产品都只选一个结果会有以下情况：

1.    10 + 20 + 40 = 70
2.    10 + 20 + 50 = 80
3.    10 + 20 + 60 = 90
4.    10 + 30 + 40 = 80
5.    10 + 30 + 50 = 90
6.    10 + 30 + 60 = 100

7.    10 + 40  = 50

8.    10 + 50  = 60

9.    10 + 60  = 70

(select 0 union (B))