3,492
社区成员
发帖
与我相关
我的任务
分享
在一个sql中完成太复杂了;添加一个函数,简化代码,当自己没有免费点的时候用来取父部门的免费点。
DROP TABLE a;
DROP TABLE bm;
DROP TABLE kc;
CREATE TABLE a (depid VARCHAR2(10),free INT,product VARCHAR2(10));
--部门 免费点 产品;
INSERT INTO A VALUES ('01', 500, '01');
INSERT INTO A VALUES ('01', 300, '02');
INSERT INTO A VALUES ('0101', 100, '01');
INSERT INTO A VALUES ('0102', 50, '02');
--注:此表中,如果某部门不存在,要用上级部门的记录,是递归向上查询的;
--部门表 bm;
CREATE TABLE bm(deptid VARCHAR2(10),parent_id VARCHAR2(10));
--编号 上级部门;
INSERT INTO BM VALUES ('01', '00');
INSERT INTO BM VALUES ('0101', '01');
INSERT INTO BM VALUES ('0102', '01');
INSERT INTO BM VALUES ('0103', '01');
--库存表 kc ;
CREATE TABLE kc(ID VARCHAR2(10),deptid VARCHAR2(10),product VARCHAR2(10),price INT);
--id 部门 产品ID 价格;
INSERT INTO KC VALUES ('0001', '0101', '01', 5000);
INSERT INTO KC VALUES ('0002', '0102', '02', 60);
INSERT INTO KC VALUES ('0003', '0103', '02', 40);
INSERT INTO KC VALUES ('0004', '0103', '01', 520);
INSERT INTO KC VALUES ('0005', '0103', '01', 110);
INSERT INTO KC VALUES ('0006', '01', '01', 120);
INSERT INTO KC VALUES ('0007', '01', '02', 700);
COMMIT;
CREATE OR REPLACE FUNCTION GET_FREE(P_DEPTID VARCHAR2, P_PRODUCT VARCHAR2)
RETURN INTEGER IS
V_RETURN INTEGER;
BEGIN
WITH M AS(
SELECT X.LL, A.PRODUCT, A.DEPID, A.FREE
FROM A,
(SELECT BM.*, LEVEL LL
FROM BM
START WITH DEPTID = P_DEPTID
CONNECT BY PRIOR PARENT_ID = DEPTID) X
WHERE A.DEPID = X.DEPTID
AND A.PRODUCT = P_PRODUCT)
SELECT FREE
INTO V_RETURN
FROM M
WHERE LL = (SELECT MIN(LL) FROM M);
RETURN(V_RETURN);
END GET_FREE;
/
SELECT KC.*
FROM KC,
(SELECT BM.PARENT_ID, BM.DEPTID, A.FREE, A.PRODUCT
FROM A, BM
WHERE A.DEPID(+) = BM.DEPTID) A
WHERE KC.PRICE > DECODE(FREE, NULL, GET_FREE(KC.DEPTID, KC.PRODUCT), FREE)
AND KC.DEPTID = A.DEPTID
AND KC.PRODUCT = DECODE(A.PRODUCT, NULL, KC.PRODUCT, A.PRODUCT)
/* AND kc.deptid='0103'
AND kc.product='01'*/
;
输出:
1 0001 0101 01 5000
2 0002 0102 02 60
3 0004 0103 01 520
4 0007 01 02 700
[Quote=引用 11 楼 wdswcy 的回复:]
看错了,应该是传递的参数。
把 NVL(GZ.QZD_JE, 0.00) > 0
AND ZSXM_DM = AV_ZSXM
AND ZSPM_DM = AV_ZSPM
的限制放到最里层查询去试试。
AND ZSXM_DM = AV_ZSXM
AND ZSPM_DM = AV_ZSPM
这两个条件不能放到最里层的,因为t_cs_sb_qzdgz 表中不存在某部门时,g.ZSXM_DM是null,这样就查不出来了他上级部门的记录了
有一表,记录某一部门的免费金额,
表名: t_cs_sb_qzdgz
如:
1 165 02 1110 5000 Y 250000000
2 300 02 1110 200 Y 250010700
3 301 02 1110 100 Y 250010703
这个免费金额又是根据部门递归向上查询的,如某一部门没有记录,要用他上级部门的金额
部门表如下:
表名: t_dm_gy_swjg
部门代码 上级部门
25001070004 Y 250010700
25001070005 Y 250010700
25001070006 Y 250010700
25001070007 Y 250010700
25001070008 Y 250010700