创建表!
DROP TABLE mytable;
CREATE TABLE mytable
(
product_code VARCHAR2(20 BYTE) NOT NULL ENABLE,
priority NUMBER NOT NULL ENABLE,
date_act DATE,
date_dis DATE
);
填充表格
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'), TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '2', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '3', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '4', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '5', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('bla', '6');
INSERT INTO mytable (product_code, priority) VALUES ('bla', '7');
现在,我只需要使用两个输入参数(或变量)product_code
和的一行date_submit
,
我使用的是OR连接器,但我没有mytable
根据date_act
和date_dis
值定义一些条件权重(确实不包含在中)。
如果condition_weight 4为true
左(3
,2
和1
)将被忽略。
如果condition_weight 4为false
且condition_weight 3为true
,则condition_weight 2和condition_weight 1将被忽略。
如果condition_weight 4和3为false
且condition_weight 2为true
condition_weight 1,则将被忽略。
如果只有condition_weight 4、3和2,将评估condition_weight 1。false
SELECT * FROM mytable
WHERE product_code = :product_code
AND (
TO_DATE (:date_submit, 'yyyy/mm/dd') BETWEEN date_act AND date_dis --condition_weight 4
OR (TO_DATE (:date_submit, 'yyyy/mm/dd') <= date_dis AND date_act IS NULL) --condition_weight 3
OR (TO_DATE (:date_submit, 'yyyy/mm/dd') >= date_act AND date_dis IS NULL) --condition_weight 2
OR (date_dis IS NULL AND date_act IS NULL) --condition_weight 1
)
AND ROWNUM <= 1
ORDER BY priority DESC
;
有需求执行此SELECT的一些想法吗?
我检查了您的“第二张图片”表,然后开始工作了...
var date_submit varchar2(12);
exec :date_submit := '2019/12/31';
var product_code varchar2(12);
exec :product_code := 'bla';
SELECT resp.*, :date_submit FROM (
SELECT 4 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') BETWEEN date_act AND date_dis
UNION
SELECT 3 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') <= date_dis AND date_act IS NULL
UNION
SELECT 2 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') >= date_act AND date_dis IS NULL
UNION
SELECT 1 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND date_dis IS NULL AND date_act IS NULL
ORDER BY condition_weight DESC, prio DESC
) resp
WHERE ROWNUM <= 1
;
输出
2019/01/15
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 0 bla 0 01-JAN-19 31-JAN-19 2019/01/15
2019/02/15
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 1 bla 1 01-FEB-19 28-FEB-19 2019/02/15
2018/12/31
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
3 5 bla 5 28-FEB-19 2018/12/31
2019/12/31
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
2 3 bla 3 01-FEB-19 2019/12/31
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句