一个博友的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);
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(价格)
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;
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
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，如需转载请自行联系原作者
