一个博友的SQL问题解决过程

一个博友的SQL问题解决过程
 
环境
 
DB2 9.1
 
问题
 
有两张表,一个产品表,一个价格表,一对多关系(一个产品可以对应多个价格),要求输出结果为 产品 当前有效价格,优惠价格(原价-当前有效价格),不符合关联的条件的记录剔除,并排序。
 
建表SQL
 
CREATE TABLE PRODUCT(PRODID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, PFORMPRICE DECIMAL(16,2),PDESC VARCHAR(50), PRIMARY KEY(PRODID));
CREATE TABLE PRICE(PRICEID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, PRRFNBR INT NOT NULL,PPRE INT NOT NULL,PRICE DECIMAL(16,2),PRIMARY KEY(PRICEID));
INSERT INTO PRODUCT(PFORMPRICE,PDESC) VALUES(10,'PROD1'),(20,'PROD2'),(30,'PROD3');
INSERT INTO PRICE(PRRFNBR,PPRE,PRICE) VALUES(1,1,1),(1,2,3),(1,3,2),(2,1,3),(2,2,2);
说明:
PRODUCT:  PRODUCTID(主键),PFORMPRICE(商品原价),PDESC(商品描述)
PRICE:  PRICEID(主键),PRRFNBR(A表主键),PPRE(价格优先级,此优先级最高的为当前有效价格),PRICE(价格)
 
数据如下
 
SELECT * FROM PRODUCT;
------------------------------------------
1
10.00
PROD1
2
20.00
PROD2
3
30.00
PROD3
SELECT * FROM PRICE;
------------------------------------------
1
1
1
1.00
2
1
2
3.00
3
1
3
2.00
4
2
1
3.00
5
2
2
2.00
 
求解过程
 
SELECT A.PRODID, A.PFORMPRICE, Z.PRICE, (A.PFORMPRICE - Z.PRICE) AS CJ
  FROM PRODUCT A
 INNER JOIN (SELECT X.*
               FROM PRICE X
              INNER JOIN (SELECT B.PRRFNBR, MAX(B.PPRE) AS TEMP
                           FROM PRICE B
                          GROUP BY B.PRRFNBR) AS Y ON X.PRRFNBR = Y.PRRFNBR
              WHERE X.PPRE = Y.TEMP) AS Z ON A.PRODID = Z.PRRFNBR;
 
执行结果
--------------------------------------
1
10.00
2.00
8.00
2
20.00
2.00
18.00
从结果可以看出,求解正确。符合要求。
 
 
另外,在我完成后,博友给出了他自己的写法:
SELECT PRODID,
       PFORMPRICE,
       (SELECT PRICE
          FROM PRICE A
         WHERE A.PRRFNBR = C.PRODID
           AND PPRE =
               (SELECT MAX(PPRE) FROM PRICE B WHERE B.PRRFNBR = A.PRRFNBR)),
       (PFORMPRICE -
       (SELECT PRICE
           FROM PRICE A
          WHERE A.PRRFNBR = C.PRODID
            AND PPRE =
                (SELECT MAX(PPRE) FROM PRICE B WHERE B.PRRFNBR = A.PRRFNBR)))
  FROM PRODUCT C
 
--------------------------------------
1
10.00
2.00
8.00
2
20.00
2.00
18.00
3
30.00
[Null]
[Null]
 
 
 
本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/40922,如需转载请自行联系原作者
上一篇:持续发力Web技术 英特尔驱动万物智能互联


下一篇:番禺区推进思科智慧城项目优化国际创新城规划