一个博友的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]