当前位置: 电脑软硬件应用网 > 设计学院 > 网络编程 > 数据库 > 正文 |
|
|||||||||||||||||||||||||||||||||||||||
一SQL问题解决过程 | |||||||||||||||||||||||||||||||||||||||
2007-9-5 12:15:04 文/leizhimi… 出处:leizhimin bolg | |||||||||||||||||||||||||||||||||||||||
环境:
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;
------------------------------------------
SELECT * FROM PRICE;
------------------------------------------
求解过程:
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; 执行结果:
--------------------------------------
从结果可以看出,求解正确。符合要求。
另外,在我完成后,博友给出了他自己的写法:
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 --------------------------------------
|
|||||||||||||||||||||||||||||||||||||||
关于45IT | About 45IT | 联系方式 | 版权声明 | 网站导航 | |
Copyright © 2003-2011 45IT. All Rights Reserved 浙ICP备09049068号 |