Oracle SQL Where条件权重

安妮塔

创建表!

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_actdate_dis定义一些条件权重(确实不包含在中

如果condition_weight 4true左(321)将被忽略。

如果condition_weight 4falsecondition_weight 3true,则condition_weight 2condition_weight 1将被忽略。

如果condition_weight 4和3falsecondition_weight 2true condition_weight 1,则将被忽略。

如果只有condition_weight 4、3和2将评估condition_weight 1false

预期结果... 在此处输入图片说明

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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章